Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann BlakeHi
I 'm not sure understood the question
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
How it can be done if the Category 3 does not exist? Can you show a
desired output ?
CREATE TABLE #Test
(
[id] INT NOT NULL PRIMARY KEY,
TextID INT NOT NULL,
LanguageID CHAR(2) NOT NULL,
CategoryID INT NOT NULL,
[Text] VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES (1,1,'en',1,'apple')
INSERT INTO #Test VALUES (2,1,'de',1,'Apfel')
INSERT INTO #Test VALUES (3,2,'fr',1,'Lundi')
INSERT INTO #Test VALUES (4,2,'de',1,'Montag')
INSERT INTO #Test VALUES (5,3,'en',2,'Food')
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>|||Your question doesn't make much sense.
"I want to select ALL the records where the LanguageID is " but then later
you say "If more than one exists, I want to select ONLY
one of these LanguageIDs ". Which is it... ALL or ONLY?
Also you don't have any rows with categoryID of 3 so there's no data even to
select.
Nik Marshall-Blank MCSD/MCDBA
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>

Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann BlakeHi
I 'm not sure understood the question
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
How it can be done if the Category 3 does not exist? Can you show a
desired output ?
CREATE TABLE #Test
(
[id] INT NOT NULL PRIMARY KEY,
TextID INT NOT NULL,
LanguageID CHAR(2) NOT NULL,
CategoryID INT NOT NULL,
[Text] VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES (1,1,'en',1,'apple')
INSERT INTO #Test VALUES (2,1,'de',1,'Apfel')
INSERT INTO #Test VALUES (3,2,'fr',1,'Lundi')
INSERT INTO #Test VALUES (4,2,'de',1,'Montag')
INSERT INTO #Test VALUES (5,3,'en',2,'Food')
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>|||Your question doesn't make much sense.
"I want to select ALL the records where the LanguageID is " but then later
you say "If more than one exists, I want to select ONLY
one of these LanguageIDs ". Which is it... ALL or ONLY?
Also you don't have any rows with categoryID of 3 so there's no data even to
select.
--
Nik Marshall-Blank MCSD/MCDBA
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>

Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann Blake
Hi
I 'm not sure understood the question
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
How it can be done if the Category 3 does not exist? Can you show a
desired output ?
CREATE TABLE #Test
(
[id] INT NOT NULL PRIMARY KEY,
TextID INT NOT NULL,
LanguageID CHAR(2) NOT NULL,
CategoryID INT NOT NULL,
[Text] VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES (1,1,'en',1,'apple')
INSERT INTO #Test VALUES (2,1,'de',1,'Apfel')
INSERT INTO #Test VALUES (3,2,'fr',1,'Lundi')
INSERT INTO #Test VALUES (4,2,'de',1,'Montag')
INSERT INTO #Test VALUES (5,3,'en',2,'Food')
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegr oups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>
|||Your question doesn't make much sense.
"I want to select ALL the records where the LanguageID is " but then later
you say "If more than one exists, I want to select ONLY
one of these LanguageIDs ". Which is it... ALL or ONLY?
Also you don't have any rows with categoryID of 3 so there's no data even to
select.
Nik Marshall-Blank MCSD/MCDBA
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegr oups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>

Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann BlakePlease post proper DDL with keys and constraints so that we don't have to
guess. I'm guessing that (textid, languageid) is unique just as I'm guessing
that ID is an IDENTITY value and is therefore probably not useful in the
solution.
SELECT id, textid, languageid, categoryid, text
FROM YourTable AS T
WHERE languageid =
(SELECT
COALESCE(MIN(CASE WHEN languageid = 'EN' THEN 'EN' END),
MIN(languageid))
FROM YourTable
WHERE textid = T.textid) ;
(untested)
David Portas
SQL Server MVP
--|||Hi Johann
Your post is inconsistent check out
http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and example
data as insert statements. It is also useful to give expected output.
Ignoring the categoryid as there is no value of 3 then you may want somethin
g
similar to:
CREATE TABLE #MyTable ( ID int NOT NULL primary key,
TextID int,
LanguageID char(2),
CategoryID int,
Text nvarchar(260) )
INSERT INTO #Mytable (ID, TextID, LanguageID, CategoryID, [Text] )
SELECT 1 , 1 , 'en' , 1 , 'apple'
UNION ALL SELECT 2, 1, 'de', 1,
'Apfel'
UNION ALL SELECT 3, 2, 'fr', 1,
'Lundi'
UNION ALL SELECT 4, 2, 'de', 1,
'Montag'
UNION ALL SELECT 5, 3, 'en', 2,
'Food'
SELECT M.Id, M.TextId, M.LanguageId, M.categoryId, M.[Text]
FROM #Mytable M
WHERE LanguageID = 'en'
UNION ALL SELECT M.Id, M.TextId, M.LanguageId, M.categoryId, M.[Text]
FROM #MyTable M
WHERE NOT EXISTS ( SELECT * FROM #MyTable D WHERE D.TextId = M.TextId AND
D.LanguageID = 'en' )
AND id = ( SELECT MIN(id) FROM #MyTable E WHERE E.TextId = M.TextId )
John
"Johann Blake" wrote:

> I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>|||I believe by showing the example data that it is clear what is unique
and what isn't. Accordingly, the TextID and LanguageID fields are NOT
unique. Thanks for your example. I needed to add the AND section to
limit the records to the CategoryID. But it works! I'll need to read up
on the COALESCE since I've never come across that before.
Thanks again
Johann Blake|||> I believe by showing the example data that it is clear what is unique
> and what isn't. Accordingly, the TextID and LanguageID fields are NOT
> unique.
Non sequitur because in your example data that compound key IS unique.
Glad if I helped anyway.
David Portas
SQL Server MVP
--

Need help comparing output

Hi everyone. I am having difficulty with the following stored procedure:
CREATE procedure A_sp_UpdatePricing_R_parts_test as
/* Declare variables */
Begin
Declare @.SellingPrice decimal(9),
@.StockCode varchar(30)
/* Declare cursor and open for processing */
Declare System_Cursor Cursor for Select StockCode from InvMaster where StockCode in ('03-18320-00')
Open System_Cursor
/* Fetch value into cursor */
Fetch Next from System_Cursor into @.StockCode
While @.@.Fetch_Status = 0
Begin
Set @.SellingPrice = Null
Select @.SellingPrice = SellingPrice from InvPrice where StockCode = @.StockCode
If @.SellingPrice >0
Update InvPrice set SellingPrice = 222
Where (StockCode = @.StockCode and StockCode like '%R')
Fetch Next from System_Cursor into @.StockCode
End
/* Close cursor */
Close System_Cursor
Deallocate System_Cursor
End
GO
What it should do is we have part numbers that end with -R or R for refurbished and most of these parts have regular part numbers that don't end in R. I am supposed to get the price for the regular one, discount it by 15% and use that to set the price for the refurbished part.
I don't know how to set the price i get for the part that is equal to the part in question but has an R in the end, which stands for refurbished.
Any help would be much appreciated.This is a multi-part message in MIME format.
--=_NextPart_000_0028_01C3C8D7.32408980
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
You don't need a cursor for this. Try:
update InvPrice
set
SellingPrice = (select SellingPrice
from InvPrice
where StockCode = '03-18320-00') * 0.85
where
StockCode like '03-18320-00%R'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Andrei" <anonymous@.discussions.microsoft.com> wrote in message
news:42E31D22-31B6-4BE5-AEBF-5DC5B34BD0A9@.microsoft.com...
Hi everyone. I am having difficulty with the following stored procedure:
CREATE procedure A_sp_UpdatePricing_R_parts_test as
/* Declare variables */
Begin
Declare @.SellingPrice decimal(9),
@.StockCode varchar(30)
/* Declare cursor and open for processing */
Declare System_Cursor Cursor for Select StockCode from InvMaster where
StockCode in ('03-18320-00')
Open System_Cursor
/* Fetch value into cursor */
Fetch Next from System_Cursor into @.StockCode
While @.@.Fetch_Status = 0
Begin
Set @.SellingPrice = Null
Select @.SellingPrice = SellingPrice from InvPrice where StockCode =@.StockCode
If @.SellingPrice >0
Update InvPrice set SellingPrice = 222
Where (StockCode = @.StockCode and StockCode
like '%R')
Fetch Next from System_Cursor into @.StockCode
End
/* Close cursor */
Close System_Cursor
Deallocate System_Cursor
End
GO
What it should do is we have part numbers that end with -R or R for
refurbished and most of these parts have regular part numbers that don't end
in R. I am supposed to get the price for the regular one, discount it by 15%
and use that to set the price for the refurbished part.
I don't know how to set the price i get for the part that is equal to the
part in question but has an R in the end, which stands for refurbished.
Any help would be much appreciated.
--=_NextPart_000_0028_01C3C8D7.32408980
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You don't need a cursor for =this. Try:
update InvPrice
set
SellingPrice ==3D (select SellingPrice
= from InvPrice
= where StockCode =3D '03-18320-00') * 0.85
where
=StockCode like '03-18320-00%R'
-- =Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Andrei" wrote in message news:42E=31D22-31B6-4BE5-AEBF-5DC5B34BD0A9@.microsoft.com...Hi everyone. I am having difficulty with the following stored procedure:CREATE procedure A_sp_UpdatePricing_R_parts_test as/* Declare variables */BeginDeclare @.SellingPrice =decimal(9),@.StockCode varchar(30) /* Declare cursor and open for processing */Declare System_Cursor Cursor for Select StockCode from =InvMaster where StockCode in ('03-18320-00')Open System_Cursor/* Fetch =value into cursor */Fetch Next from System_Cursor into =@.StockCodeWhile @.@.Fetch_Status =3D 0BeginSet @.SellingPrice =3D NullSelect @.SellingPrice =3D SellingPrice from =InvPrice where StockCode =3D @.StockCodeIf @.SellingPrice >0  =; = Update InvPrice set SellingPrice =3D 222 &nbs=p;  =; = &nb=sp; &nbs=p; Where (StockCode =3D @.StockCode and StockCode like '%R') Fetch Next from System_Cursor into @.StockCodeEnd/* Close cursor */Close System_CursorDeallocate System_CursorEndGOWhat =it should do is we have part numbers that end with -R or R for refurbished and =most of these parts have regular part numbers that don't end in R. I am supposed =to get the price for the regular one, discount it by 15% and use that to set =the price for the refurbished part.I don't know how to set the price i get =for the part that is equal to the part in question but has an R in the end, =which stands for refurbished. Any help would be much =appreciated.

---

--=_NextPart_000_0028_01C3C8D7.32408980--

Need Emergency Help with SQL Query (difficulty: moderate)

Panicked, as usual. Who says it's great being a sole proprietor?

Using MS-SQLServer:

Three tables: A_Appointment, AX_Appointment_Entity and E_Entity.
AX_Appointment_Entity is an intersect/association table between
A_Appointment and E_Entity. One appointment may have many attendees
(Appointment_Entity). One attendee (Entity) may have many
appointments.

My task is to delete all appointments and related
AX_Appointment_Entity rows where the number of attendees is one or
less.

I'm great at standard select or delete queries. Because this straddles
tables in a way I'm less skilled in, I'm pleading for help.

Thanks for any ideas. fwiw, I'm using Transact-SQL.

BLink
--------
"The worst thing about censorship is [redacted]"Brian Link wrote:

Quote:

Originally Posted by

Three tables: A_Appointment, AX_Appointment_Entity and E_Entity.
AX_Appointment_Entity is an intersect/association table between
A_Appointment and E_Entity. One appointment may have many attendees
(Appointment_Entity). One attendee (Entity) may have many
appointments.
>
My task is to delete all appointments and related
AX_Appointment_Entity rows where the number of attendees is one or
less.

If deletions from A_Appointment cascade to AX_AppointmentEntry:

delete from A_Appointment
where AppointmentKey in (
select AppointmentKey
from AX_Appointment_Entity
group by AppointmentKey
having count(*) <= 1
)

If deletions from A_Appointment are blocked until the matches in
AX_Appointment_Entity are also deleted:

select AppointmentKey
into #appointments_to_delete
from AX_Appointment_Entity
group by AppointmentKey
having count(*) <= 1

delete from AX_Appointment_Entity
where AppointmentKey in (
select AppointmentKey
from #appointments_to_delete
)

delete from A_Appointments
where AppointmentKey in (
select AppointmentKey
from #appointments_to_delete
)

If deletions from A_Appointment leave orphans in AX_Appointment_Entity
(why?):

delete from A_Appointment
where AppointmentKey in (
select AppointmentKey
from AX_Appointment_Entity
group by AppointmentKey
having count(*) <= 1
)

delete from Ax_Appointment_Entity
where AppointmentKey in (
select AppointmentKey
from AX_Appointment_Entity
group by AppointmentKey
having count(*) <= 1
)|||Here is one solution:

Delete Appointment a
Where exists (select 'hi there'
From Entity e
Where a.attendee = e. attendee
Group by e. attendee
Having count(e. attendee) <= 1)

You can do the similar step with the intersect table
Appointment_Entity.

Brian Link wrote:

Quote:

Originally Posted by

Panicked, as usual. Who says it's great being a sole proprietor?
>
Using MS-SQLServer:
>
Three tables: A_Appointment, AX_Appointment_Entity and E_Entity.
AX_Appointment_Entity is an intersect/association table between
A_Appointment and E_Entity. One appointment may have many attendees
(Appointment_Entity). One attendee (Entity) may have many
appointments.
>
My task is to delete all appointments and related
AX_Appointment_Entity rows where the number of attendees is one or
less.
>
I'm great at standard select or delete queries. Because this straddles
tables in a way I'm less skilled in, I'm pleading for help.
>
Thanks for any ideas. fwiw, I'm using Transact-SQL.
>
BLink
--------
"The worst thing about censorship is [redacted]"

|||othellomy@.yahoo.com wrote:

Quote:

Originally Posted by

Delete Appointment a
Where exists (select 'hi there'
From Entity e
Where a.attendee = e. attendee
Group by e. attendee
Having count(e. attendee) <= 1)

A_Appointment doesn't have an 'attendee' column. Original post: "One
appointment may have multiple attendees (Appointment_Entity)".

Reworking this to use AX_Appointment_Entity (and to change 'delete' to
'delete from') ought to lead to another valid solution.|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Guessing at what you meant to post, and using data
element names that make sense, does your schema look like this?

CREATE TABLE Appointments
(appt_id INTEGER NOT NULL PRIMARY KEY,
appt_date DATETIME NOT NULL,
...);

CREATE TABLE Attendees
(attendee_id INTEGER NOT NULL PRIMARY KEY,
attendee_name VARCHAR(20) NOT NULL,
..);

CREATE TABLE Attendance
(appt_id INTEGER NOT NULL
REFERENCES Appointments (appt_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
attendee_id INTEGER NOT NULL
REFERENCES Attendees (attendee_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (appt_id, attendee_id));

1)You never use "-key" as a suffix. That tells us HOW the data
element is used and not WHAT the data element is. Likewise, you never
put silly prefixes that tell WHERE there is an appearance or what the
data type used is.

2)The name "entity" is too vague to be data element name. Do you
also use "thingie" or "Whatyamacallit" in your data dictionary?
Use particular names, not vague ones. If you cannot thik of a good
name, then you do not have a clear idea of what you are doing yet.

3)A table models a set of like entities and therefore should have a
collective or plural name. Unless they really do model one thing.

4)The correct term is a relationship table, in this case a
many-to-many relationship. There is not such thing as an intersection
or association table.

5)Notice that you do most of your work with DRI actions, not in code.
The idea of a RDBMS is that it keeps data integrity for you.

Quote:

Originally Posted by

Quote:

Originally Posted by

>My task is to delete all appointments and related Attendance rows where the number of attendees is one or less. <<

DELETE FROM Appointments
WHERE appt_id
IN (SELECT A1.appt_id
FROM Attendance AS A1
GROUP BY A1.appt_id
HAVING COUNT(*) <= 1);

The DRI makes < 1 redundant, but it preserves what you posted. The DRI
actions will re-arrange the Attendance as people or appointments drop
out.|||--CELKO-- wrote:

Quote:

Originally Posted by

1)You never use "-key" as a suffix. That tells us HOW the data
element is used and not WHAT the data element is.

I work with an established package that uses "-key" as a suffix
regularly, but always for artificial keys (so "what the data element
is" is established across the board by convention as "nothing the
end user has any business seeing").

Quote:

Originally Posted by

5)Notice that you do most of your work with DRI actions, not in code.
The idea of a RDBMS is that it keeps data integrity for you.

[snip]

Quote:

Originally Posted by

The DRI makes < 1 redundant, but it preserves what you posted. The DRI
actions will re-arrange the Attendance as people or appointments drop
out.

If they drop out completely. If a person drops one appointment but
retains others, then the system should directly delete the Attendance
row corresponding to the dropped person+appointment pair.|||You have three possible actions which should be in separate procedures

CREATE PROCEDURE DelAppointment (@.myappt_id INTEGER)
DELETE FROM Appointments -- meeting cancelled
WHERE appt_id = @.my_appt_id;

CREATE PROCEDURE DelAttendee(@.my_attendee_id INTEGER)
DELETE FROM Attendees -- employee gone
WHERE attendee_id = @.my_attendee_id;

CREATE PROCEDURE DelAttendance
(@.my_attendee_id INTEGER , @.myappt_id INTEGER)
BEGIN
DELETE FROM Attendance -- employee removed from meeting
WHERE appt_id = @.my_appt_id
AND attendee_id = @.my_attendee_id;
-- if the was the next to last guy, cancel the meeting
DELETE FROM Appointments
WHERE appt_id
IN (SELECT A1.appt_id
FROM Attendance AS A1
GROUP BY A1.appt_id
HAVING COUNT(*) <= 1);
END;

CREATE PROCEDURE
(@.my_attendee_id INTEGER , @.myappt_id INTEGER)

Watch your error handling and transaction level in the last procedure.