sql server new users: Help with a Transact-SQL query (difficulty: medium) -- sql server new users

Re: Help with a Transact-SQL query (difficulty: medium)

Arnie Rowland
11/29/2006 10:00:07 PM

sql server new users: It would be nice to have the table DDL, and perhaps some sample data in =
the form of input statements. (See: http://www.aspfaq.com/5006 )

In general, (not knowing your column names), something like this:

DELETE A_Appointment
WHERE A_Appointment.PK =3D ( SELECT Appointment_FK
FROM AX_Appointment_Entity=20
GROUP BY Appointment_FK
HAVING Appointment_FK =3D 1
)

DELETE AX_Appointment_Entity=20
WEHRE Appointment_FK =3D ( SELECT Appointment_FK
FROM AX_Appointment_Entity=20
GROUP BY Appointment_FK
HAVING Appointment_FK =3D 1
)

This does NOT delete anything from E_Entity.

--=20
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.=20
Most experience comes from bad judgment.=20
- Anonymous

You can't help someone get up a hill without getting a little closer to =
the top yourself.
- H. Norman Schwarzkopf

[quoted text, click to view]

Help with a Transact-SQL query (difficulty: medium)

Brian Link
11/29/2006 10:31:36 PM

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

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.

Can anyone suggest pseudo-code or more targeted SQL code to help me
out?

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

BLink
--------------------------

Re: Help with a Transact-SQL query (difficulty: medium)

Arnie Rowland
11/30/2006 2:04:18 PM

Thanks Hugo, yes IN should be used instead of [=].

Now where is that darn DWIM key on the keyboard...

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf

[quoted text, click to view]

Re: Help with a Transact-SQL query (difficulty: medium)

Arnie Rowland
11/30/2006 2:20:09 PM

wehre: rebuttments, diversions, dams, nets placed across streams or rivers,
actually kind of like a filter... (or a WHERE).

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf

[quoted text, click to view]

Re: Help with a Transact-SQL query (difficulty: medium)

Raymond D'Anjou
11/30/2006 5:04:00 PM

Or he can use a 'WHERE EXISTS'.
You also forgot to mention that the 'WHERE' in the second delete is spelled
'WEHRE'
which has a completely different definition.

From Google definition:
für Gewehre, I. 43. Wehrgeschmeide, III. 4, für Waffenschmuck,
Waffenrüstung. - Wörter, die in der Dichtersprache erhalten zu werden
verdienen.

Now if someone can translate that to English...

[quoted text, click to view]

Re: Help with a Transact-SQL query (difficulty: medium)

Hugo Kornelis
11/30/2006 10:52:58 PM

[quoted text, click to view]

Hi Arnie,

I think you meant to use IN instead of = in those two queries.

(Time for another coffee shot??)

--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server new users group.