I didn't examine the query too well in the first place. The outer join doesn't work because of the filter on the table you want nulls in. I would suggest a UNION instead of an outer join. I'm not sure it's the most efficient solution though.
SELECT Q.QueueID, XUQ.AccessID, Q.QueueName
FROM Queues Q
JOIN SecurityXrefUserQueues XUQ
ON XUQ.QueueID = Q.QueueID
WHERE XUQ.UserID = <cfqueryparam cfsqltype="CF_SQL_NUMERIC" value="#url.uid#">
UNION
SELECT Q.QueueID, NULL AS AccessID, Q.QueueName
FROM Queues Q
WHERE Q.QueueID NOT IN (
SELECT XUQ.QueueID
FROM SecurityXrefUserQueues XUQ
WHERE XUQ.UserID = <cfqueryparam cfsqltype="CF_SQL_NUMERIC" value="#url.uid#">
)
Pascal
-----Oorspronkelijk bericht-----
Van: Cedric Villat [mailto:[EMAIL PROTECTED]
Verzonden: za 15/11/2003 17:12
Aan: CF-Talk
CC:
Onderwerp: Re:JOIN help
No such luck. I had tried that earlier, but tried again just to make sure. A left and right join just return 1 row:
UserID QueueID AccessID
2 1 0
I never get the NULL row. Any more ideas?
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
