?
Solved

Double Left Join?

Posted on 2003-03-08
7
Medium Priority
?
2,203 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:weeezl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 2

Author Comment

by:weeezl
ID: 8094773
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
 
LVL 13

Accepted Solution

by:
ispaleny earned 800 total points
ID: 8095010
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
 

Expert Comment

by:Gerardo_Grignoli
ID: 8098365
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 2

Author Comment

by:weeezl
ID: 8112606
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
 

Expert Comment

by:CleanupPing
ID: 9276050
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
 
LVL 12

Expert Comment

by:monosodiumg
ID: 11042479
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

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question