Double Left Join?

I want to get values out of a group of tables but want to get null values in 2 instances if either join doesn't match on one table. I don't know the correct syntax in whic to write the query.

Here's the basic idea:


select au_key_content.input_content_id,au_key_content.key_id,au_row.row_id
from au_datatype,au_input_content,au_row
LEFT JOIN au_key_content on au_key_content.input_content_id=au_input_content.input_content_id
         AND au_key_content.row_id=au_row.row_id
WHERE au_row.content_id=#content# AND au_row.content_id=au_input_content.content_id
order by au_row.row_id

Of course this doesn't work because you can't write the join this way referencing two different databases in the same join statement. How would I write this to work so that au_key_content is joined conditionally on both tables? I want to get Null values for the keys if either join fails.
LVL 2
weeezlAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

weeezlAuthor Commented:
Sorry... one extra table reference

select au_key_content.input_content_id,au_key_content.key_id,au_row.row_id
from au_input_content,au_row
LEFT JOIN au_key_content on au_key_content.input_content_id=au_input_content.input_content_id
        AND au_key_content.row_id=au_row.row_id
WHERE au_row.content_id=#content# AND au_row.content_id=au_input_content.content_id
order by au_row.row_id
0
ispalenyCommented:
Something like this?

select au_key_content.input_content_id,au_key_content.key_id,au_row.row_id
from au_input_content
join au_row
 on au_row.content_id=au_input_content.content_id
and au_row.content_id=#content#
full join au_key_content
 on au_key_content.input_content_id=au_input_content.input_content_id
and au_key_content.row_id          =au_row.row_id
order by au_row.row_id
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gerardo_GrignoliCommented:
Unsure about your db design, its either wrong formed, or redundantly joined in the query, or i dont understand your model...
anyway, i think that removing one of the wrongs 'join' should work...

Select
au_key_content.input_content_id,
au_key_content.key_id,
au_row.row_id

from au_input_content

LEFT JOIN au_row
     au_row.content_id=au_input_content.content_id

LEFT JOIN au_key_content on
       au_key_content.row_id=au_row.row_id

WHERE au_row.content_id=#content#
order by au_row.row_id


-----
Maybe i'm dumb and didnt get the meaning of the question... so also try this:

SELECT
(CASE au_key_content.input_content_id=au_input_content.input_content_id WHEN 1 THEN au_key_content.input_content_id
ELSE NULL END) AS input_content_id,

(CASE au_key_content.input_content_id=au_input_content.input_content_id WHEN 1 THEN au_key_content.key_id
ELSE NULL END) AS key_id,
au_row.row_id

from au_input_content

LEFT JOIN au_row
     au_row.content_id=au_input_content.content_id

LEFT JOIN au_key_content on
       au_key_content.row_id=au_row.row_id

WHERE au_row.content_id=#content#
order by au_row.row_id

good luck
Gerardo.-
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

weeezlAuthor Commented:
works fine when I use the full join syntax:

SELECT     au_row.row_id, au_input_content.input_content_id, au_key_content.key_id
FROM         au_input_content INNER JOIN
                      au_row ON au_input_content.content_id = au_row.content_id LEFT OUTER JOIN
                      au_key_content ON au_key_content.row_id = au_row.row_id AND au_input_content.input_content_id = au_key_content.input_content_id
WHERE     (au_input_content.content_id = #content#)
0
CleanupPingCommented:
weeezl:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
monosodiumgCommented:
No comment has been added to this question in more than 251 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: ispaleny http:#8095010

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

monosodiumg
EE Cleanup Volunteer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.