Dumb SQL question....

Question

Dumb SQL question....

CR Doden

Old Hand

Points: 312
More actions
May 13, 2004 at 1:14 pm

#85662

I have the following query that retrieves a list of users that belong to a particular group (in this case groupid 1).
SELECT su.sysuser_firstname, su.sysuser_lastname, sug.sysuser_id, sug.sysusergroup_id
  FROM sysuser_sysusergroup sug, sysuser su
  WHERE sysusergroup_id = '1'
  AND sug.sysuser_id = su.sysuser_id
  ORDER BY su.sysuser_lastname, su.sysuser_firstname
I need to figure out a query that will return only users that are NOT in group 1. I know this should be easy for me but I'm finding myself in syntax hell or getting otherwise unwanted results. I tried using:
SELECT distinct sug.sysuser_id, su.sysuser_firstname, su.sysuser_lastname, sysusergroup_id
  FROM sysuser_sysusergroup sug, sysuser su
  WHERE NOT sysusergroup_id = '#form.groupid#'
  AND sug.sysuser_id = su.sysuser_id
  AND sug.sysuser_id in (select DISTINCT(sysuser_id) from sysuser_sysusergroup)
  ORDER BY su.sysuser_lastname, su.sysuser_firstname
But this is not returning users as distinct. Any thoughts? Thanks in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246993 More actions · Answer 1

The sysuser_sysusergroup and sysuser tables do not even exist on my SQL Server 2000 installation – are they standard? I'm sure that I could help out if I understood the table structures and relationships

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

David Burrows SSC Guru Points: 65144 More actions · Answer 2

SELECT su.sysuser_id, su.sysuser_firstname, su.sysuser_lastname

FROM sysuser su

LEFT OUTER JOIN sysuser_sysusergroup sug

ON sug.sysuser_id = su.sysuser_id

AND sug.sysusergroup_id = '1'

WHERE sug.sysuser_id IS NULL

ORDER BY su.sysuser_lastname, su.sysuser_firstname

or

SELECT su.sysuser_id, su.sysuser_firstname, su.sysuser_lastname

FROM sysuser su

WHERE NOT EXISTS(SELECT 1 FROM sysuser_sysusergroup sug

WHERE sug.sysuser_id = su.sysuser_id

AND sug.sysusergroup_id = '1')

ORDER BY su.sysuser_lastname, su.sysuser_firstname

BTW, there is no such thing as a dumb question. It is a term made up by smart a$$'s who think they know it all.

Far away is close at hand in the images of elsewhere.
Anon.

CR Doden Old Hand Points: 312 More actions · Answer 3

Sweet! Thanks David, did exactly what I needed it to do!

Phil, sorry I wasn't more clear, these are user tables not sys, but the point is now moot. Thanks for the reply anywho.

SteveB-74825 Hall of Fame Points: 3290 More actions · Answer 4

i was going to suggest:

SELECT su.sysuser_firstname, su.sysuser_lastname, sug.sysuser_id, sug.sysusergroup_id

FROM sysuser_sysusergroup sug, sysuser su

WHERE sysusergroup_id != '1'

AND sug.sysuser_id = su.sysuser_id

ORDER BY su.sysuser_lastname, su.sysuser_firstname

for info != is the same as 'not equals to'

however using not exists is a more efficient way of writing the query.

loved the point about smart a$$es !

cheers

dbgeezer