CODEWITHSUNDEEP
sqlsql-server
bmgh1985
bmgh1985

Reputation: 789

How to get one row to always display (using one view)

I have the following (much simplified) view:

SELECT        T1.item, T2.text
FROM            T1 LEFT OUTER JOIN
                         T2 ON T1.code = T2.code AND T1.item = T2.item
WHERE        (T1.item = '12345') AND (T1.code = '678') AND (T2.detailID = '99')

T1

╔═══════╦═══════╦═══════════════╗
║ code  ║ item  ║ other info... ║
╠═══════╬═══════╬═══════════════╣
║ code1 ║ item1 ║ other info... ║
║ code2 ║ item2 ║ other info... ║
╚═══════╩═══════╩═══════════════╝

T2

╔═══════╦═══════╦═══════════╦═══════╦═══════════════╗
║ code  ║ item  ║ detailID  ║ text  ║  other info   ║
╠═══════╬═══════╬═══════════╬═══════╬═══════════════╣
║ code1 ║ item1 ║ detailID1 ║ text1 ║ other info... ║
║ code1 ║ item1 ║ detailID2 ║ text2 ║ other info... ║
║ code1 ║ item1 ║ detailID3 ║ text3 ║ other info... ║
║ code2 ║ item2 ║ detailID1 ║ text4 ║ other info... ║
╚═══════╩═══════╩═══════════╩═══════╩═══════════════╝

NB: detailID is reused on multiple codes/items

In most instances this works fine. Except that on some occasions, detailID 99 does not exist for a code and item. When that happens, it returns 0 rows (I thought the LEFT OUTER JOIN would fix that but my SQL is very amateurish). How can I get it so it does return a row, but with just

12345, ''

as the result in that instance?

Upvotes: 2

Views: 101

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You need to move the condition on the second table to the on clause:

SELECT T1.item, T2.text
FROM T1 LEFT OUTER JOIN
     T2
     ON T1.code = T2.code AND T1.item = T2.item AND T2.detailID = '99'
WHERE (T1.item = '12345') AND (T1.code = '678') ;

When there is no match, then the value is NULL and your original where clause filters it out.

Upvotes: 6

Related Questions