weeezl
asked on
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_conte
from au_datatype,au_input_conte
LEFT JOIN au_key_content on au_key_content.input_conte
AND au_key_content.row_id=au_r
WHERE au_row.content_id=#content
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.
Here's the basic idea:
select au_key_content.input_conte
from au_datatype,au_input_conte
LEFT JOIN au_key_content on au_key_content.input_conte
AND au_key_content.row_id=au_r
WHERE au_row.content_id=#content
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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_conte
au_key_content.key_id,
au_row.row_id
from au_input_content
LEFT JOIN au_row
au_row.content_id=au_input
LEFT JOIN au_key_content on
au_key_content.row_id=au_r
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_conte
ELSE NULL END) AS input_content_id,
(CASE au_key_content.input_conte
ELSE NULL END) AS key_id,
au_row.row_id
from au_input_content
LEFT JOIN au_row
au_row.content_id=au_input
LEFT JOIN au_key_content on
au_key_content.row_id=au_r
WHERE au_row.content_id=#content
order by au_row.row_id
good luck
Gerardo.-
anyway, i think that removing one of the wrongs 'join' should work...
Select
au_key_content.input_conte
au_key_content.key_id,
au_row.row_id
from au_input_content
LEFT JOIN au_row
au_row.content_id=au_input
LEFT JOIN au_key_content on
au_key_content.row_id=au_r
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_conte
ELSE NULL END) AS input_content_id,
(CASE au_key_content.input_conte
ELSE NULL END) AS key_id,
au_row.row_id
from au_input_content
LEFT JOIN au_row
au_row.content_id=au_input
LEFT JOIN au_key_content on
au_key_content.row_id=au_r
WHERE au_row.content_id=#content
order by au_row.row_id
good luck
Gerardo.-
ASKER
works fine when I use the full join syntax:
SELECT au_row.row_id, au_input_content.input_con
FROM au_input_content INNER JOIN
au_row ON au_input_content.content_i
au_key_content ON au_key_content.row_id = au_row.row_id AND au_input_content.input_con
WHERE (au_input_content.content_
SELECT au_row.row_id, au_input_content.input_con
FROM au_input_content INNER JOIN
au_row ON au_input_content.content_i
au_key_content ON au_key_content.row_id = au_row.row_id AND au_input_content.input_con
WHERE (au_input_content.content_
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.
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.
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
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
ASKER
select au_key_content.input_conte
from au_input_content,au_row
LEFT JOIN au_key_content on au_key_content.input_conte
AND au_key_content.row_id=au_r
WHERE au_row.content_id=#content
order by au_row.row_id