I'm really looking for a solution with only one query using joins. It should
be possible to do this with a couple of joins.
This query, suggested by Steve, would work well if the tables remained
small:
SELECT user_id,user_name,doc_id,doc_name FROM USERS,DOCS WHERE NOT EXISTS
( SELECT signoff_id FROM USER_DOCS WHERE user_id =
USERS.user_id AND doc_id = DOCS.doc_id )
In large tables, though, I don't think it's going to be a very good
solution.
It seems like something along the lines of this should work:
select u.user_name, d.doc_name
from docs d left join(
users u left join user_docs ud on u.user_id = ud.user_id
)
where ud.user_id is null
Actually, I just tried that and it does work on my test db, which is mysql.
Strangely enough, I tried it on the production db which is SQL Server
(tables are different, but relationships are the same) and it doesn't work.
Anybody know why this doesn't work in SQL Server 2K???
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300794
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
