Query question
-
**EDIT wow, just noticed that chopped off half my post and my whole question....here's goes again...
Well you guys hooked me up before so I return with another question. This is much like my last question just using different tables.
I have the following query, which returns all the groups a particlular user is in. The 3 tables that make up the user/group relationship are sysuser, sysusergroup, and sysuser_sysusergroup.
SELECT susg.sysusergroup_id, sg.sysusergroup_name
FROM sysuser_sysusergroup susg, sysusergroup sg
WHERE sysuser_id = '1'
AND susg.sysusergroup_id = sg.sysusergroup_id
I tried studying the answer code from my last question but it only made me more confused. What I need is a way to get all the groups that a user is NOT associated with. So in essence, the exact opposite of what the above query does, and return resultset with distinct group ids.
Sorry bout that chopped post. Anway, many thanks in advance if you can help.
-
Sorry to bump this, but first try on post was missing half the text I typed in.
-
Ok, first some sql theory. A join is another (faster) way of doing a where clause between two tables. So where you say:
SELECT susg.sysusergroup_id, sg.sysusergroup_name
FROM sysuser_sysusergroup susg, sysusergroup sg
WHERE sysuser_id = '1'
AND susg.sysusergroup_id = sg.sysusergroup_id
You could also say:
SELECT susg.sysusergroup_id, sg.sysusergroup_name
FROM sysuser_sysusergroup susg INNER JOIN
sysusergroup sg on susg.sysusergroup_id = sg.sysusergroup_id
WHERE sysuser_id = '1'
In this case, it does the same thing. However, one advantage of a join is that you can use an OUTER JOIN, which means you include all the rows from one table, and only the rows from another table where the join matches. In this case, we can use:
SELECT susg.sysusergroup_id, sg.sysusergroup_name
FROM sysuser_sysusergroup susg RIGHT OUTER JOIN
sysusergroup sg on susg.sysusergroup_id = sg.sysusergroup_id
WHERE sysuser_id = '1'
It would mean to include all the rows from sg and also the data from susg where the id columns match. If there is no corresponding data in susg, then the susg.sysusergroup_id cell will be NULL.
Now, in order to get only the ones where there is no match, we need to filter the susg table to items where the column we joined on is NULL. That is, we only want the rows where susg.sysusergroup_id is NULL:
SELECT susg.sysusergroup_id, sg.sysusergroup_name
FROM sysuser_sysusergroup susg RIGHT OUTER JOIN
sysusergroup sg on susg.sysusergroup_id = sg.sysusergroup_id
WHERE (sysuser_id = '1') and (susg.sysusergroup_id IS NULL)
I hope this accomplishes what you are after. Of course, I haven't tried this out, so I might have the join round the wrong way. In any case, you should really learn about joins, (inner, outer and full) and why they are so useful.
pk
-
SELECT sg.sysusergroup_id, sg.sysusergroup_name
FROM sysusergroup sg
WHERE sg.sysusergroup_id NOT IN(
SELECT susg.sysusergroup_id
FROM sysuser_sysusergroup susg
WHERE sysuser_id = '1' )
Just as an alternative to the outer join technique.
/rockmoose
You must unlearn what You have learnt -
Thanks guys!
Both worked, though I had to fiddle with phil's query a bit, which is a good thing since I learned a lot about joins
. Out of curiousity, which of these methods is faster? I don't have enough data to test timing differences.Cheers!
-
It is considered better overall to use the join method.
The reason is it takes some work off the query engine with regards to decisiions it must make when trying to join 2 tables in the older method with the join predicates in the where clause.
As for the "in" syntax item, it will build a summary table and more or less create a join similar to the "join" syntax example that it will work from in most cases.
Look at the execution plan to see the decisions it makes. All the examples may actually have the same plan.
-
For completeness we might add the EXISTS method.
SELECT sg.sysusergroup_id, sg.sysusergroup_name
FROM sysusergroup sg
WHERE NOT EXISTS(
SELECT * FROM sysuser_sysusergroup susg
WHERE susg.sysuser_id = '1' AND sg.sysusergroup_id = susg.sysusergroup_id 
Performancewise the EXISTS method is better than IN method.
It is good to have a choice of techniques to accomplish things in.
For small tables and ad hoc queries against the database I personally
just use the technique that seems most convenient at the time.
But Ok, the IN clause is not the best technique, ( Just simplish to write sometimes
)Happy SQL-ing
/rockmoose
You must unlearn what You have learnt -
Actually that cn be dependent on several factors including index.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply