JOIN question 1

[kevinforbes]

Hi All,

Quick question, probably an easy answer but my brain thinks it today is Monday...

Here is my query:

SELECT blah
FROM tbl1
INNER JOIN tbl2 ON
tbl1.id = tbl2.id
WHERE tbl1.id = 'blah' AND tbl2.other_id = 'other_blah'

Works fine except when tbl2.other_id doesn't match the criteria.

I always want to return a record, wether or not tbl2 is matched.

Thanks,
Kev

 

[tlbroadbent]

Use an OUTER JOIN.

SELECT blah
FROM tbl1
LEFT JOIN tbl2
ON tbl1.id = tbl2.id
WHERE tbl1.id = 'blah'
AND (tbl2.other_id = 'other_blah' OR tbl2.other_id Is Null)
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[kevinforbes]

Thanks Terry,

This works except that I don't want to return tbl2.other_id if it does not = 1 (tbl1.other_id can only be 1 or 0), however, I still want to return the record from tbl1 every time.

Also, I only want to return 1 record every time, I know MySQL uses a "Limit" clause, anything like that in SQL Server?

thanks,
Kev

 

[kevinforbes]

Sorry for the confusion..paragraph 1 should read like this:

(tbl2.other_id can only be 1 or 0)


k

 

[tlbroadbent]

Use TOP to limit the number of rows returned.

What do you mean by "I don't want to return tbl2.other_id if it does not = 1?" Do you want to return a null value or not see the column in the result set?

SELECT top 1 blah
FROM tbl1
LEFT JOIN tbl2
ON tbl1.id = tbl2.id
WHERE tbl1.id = 'blah'
AND (tbl2.other_id = 'other_blah' OR tbl2.id Is Null)

It would be helpful if you posted real names for columns and tables because "blah" doesn't really help us to see what you want as a result - 1 column, multiple columns, from which tables, etc. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[kevinforbes]

How about this...

SELECT top 1 ships.*, images.id, images.isPrimary, images.description
FROM ships

ONLY DO A JOIN IF images.isPrimary = 1

LEFT JOIN images
ON ships.ref_id = images.ref_id
WHERE ships.ref_id = '003'
AND (images.isPrimary = 1 OR images.isPrimary Is Null)

Does that logic make sense? Basically I only want to join the images table if there is a 1 in the images.isPrimary column.

tx,
Kev

 

[tlbroadbent]

You have to perform the join in order to get the matching record from the images table in order to test if isPrimary = 1. If you use the LEFT JOIN, you'll only join to an images row where IsPrimary = 1. Otherwise, the query will return NULL in the columns from the images table. I have one recommended change to your query. Test if the JOIN column (ref_id) is NULL rather than the isPrimary column.

SELECT top 1 ships.*, images.id, images.isPrimary, images.description
FROM ships
LEFT JOIN images
ON ships.ref_id = images.ref_id
WHERE ships.ref_id = '003'
AND (images.isPrimary = 1 OR images.ref_id Is Null)

You can also write the query as follows. Sometimes this form is easier to understand.

SELECT top 1 s.*, i.id, i.isPrimary, i.description
FROM ships s
LEFT JOIN
(Select ref_id, id, isPrimary, description
From images Where images.isPrimary = 1) i
ON s.ref_id = i.ref_id
WHERE s.ref_id = '003' Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[kevinforbes]

Thanks Terry, I will give that a try.

Kev