This query will return all users and docs that have a relationship
established but don't have a sign-off record:
SELECT u.user_name
, d.doc_name
FROM user_docs ud
INNER JOIN users u
ON ud.user_id = u.user_id
INNER JOIN docs d
ON ud.doc_id = d.doc_id
WHERE ud.signoff_id IS NULL
It will not return records that have non-matching foreign keys (non-existent
user or doc). Converting the INNER JOINS to LEFT OUTER JOINS would change
that behavior.
On Mon, Mar 10, 2008 at 8:39 AM, Sonny Savage <[EMAIL PROTECTED]> wrote:
> Question: Do you also want a result in the case where there is no
> USER_DOCS record for a given USER and DOC record (FULL OUTER JOIN)?
>
>
> On Sat, Mar 8, 2008 at 12:12 AM, Josh McKinley <[EMAIL PROTECTED]> wrote:
>
> > I've got three tables like this (simplified):
> >
> > USERS
> > user_id
> > user_name
> >
> > DOCS
> > doc_id
> > doc_name
> >
> > USER_DOCS
> > signoff_id
> > user_id
> > doc_id
> >
> > I need all user names and doc names where the combination doesn't exist
> > in the signoff table USER_DOCS. For example, user 12 hasn't signed of on doc
> > 9 and user 6 hasn't signed off on doc 44, I need to know that.
> >
> >
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:300844
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
