Dumb SQL question....
-
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.
-
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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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. -
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.
-
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply