A left outer join?
-Oorspronkelijk bericht-
Van: Cedric Villat [mailto:[EMAIL PROTECTED]
Verzonden: za 15/11/2003 2:52
Aan: CF-Talk
CC:
Onderwerp: JOIN help
Here is my query now, but all it returns are rows in which there are entries
in the UserQueues table.
SELECT Q.Qu
> What I need is to select
> User 1 and ALL Queues so I get a result like this:
>
> UserID QueueID AccessID
> 1 2 0
> 1 1 NULL
You could use an intermediate or temp table to return your
desired result set like this:
/* create
Cedric,
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
Pascal Peters wrote:
> SELECT Q.QueueID, XUQ.AccessID, Q.QueueName
> FROM Queues Q
> JOIN SecurityXrefUserQueues XUQ
> ON XUQ.QueueID = Q.QueueID
> WHERE XUQ.UserID =
> UNION
> SELECT Q.QueueID, NULL AS AccessID, Q.QueueName
> FROM Queues Q
> WHERE Q.QueueID NOT IN (
> SELECT XUQ.QueueID
Jochem,
Thanks, that worked like a champ. I had a solution using 2 queries, but that
wasn't really elegant. This should be much better. Thanks!
Cedric
> Subject: JOIN help
> From: Jochem van Dieten <[EMAIL PROTECTED]>
> Date: Sun, 16 Nov 2003 13:53:17 +0100
> Thread:
http://www.houseoffusion.com
> I am a bit new to JOINs and I am having trouble with this
> JOIN. I
> don't get an error until the last AND and then it says:
> [Macromedia][Oracle JDBC Driver][Oracle]ORA-00933: SQL
> command not
> properly ended
> SELECT
> empower_local_case_studies.*,empower_registration.email
> FROM empower_
>You should probably qualify the columns in your where clause, and with
>table-names of this length, I would definately use aliases. See if
>this helps any.
I was going to, but didn't remember right-off how to do so. I've done so now,
thanks.
>SELECT ecs.*,er.email
>FROM empower_local_case_stud
It's no the primary keys that need to be =. What you need is to have a
foreign key in the table that holds the email. So, for example, you'd
have:
empower_local_case_studies
studyid (PK)
name
etc
empower_registration
registrationid (PK)
studyid (FK)
etc.
Then, you'd join:
WHERE erc.studyid = er.
>>Does the registration_id really match the
>>local_case_study_id? -- it
>>looks like you're joining the primary key on both
>>tables... not that
>>doing that would have to be invalid, but this doesn't seem
>>like the
>>sort of situation where I would expect that.
> I thought that this is how I re
ah that's what I'm doing. I was doing it wrong, but went to a meeting and the
wrong information displayed. I was aligning primary keys, you are right, and
should've been doing a foreign key to a primary key. Thanks Deanna!
>It's no the primary keys that need to be =. What you need is to have
10 matches
Mail list logo
