Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Double Left Join?

Posted on 2003-03-08
7
Medium Priority
?
2,220 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
6 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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

580 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