CODEWITHSUNDEEP
sqlsql-server-2005t-sqljoin
Saanch
Saanch

Reputation: 1844

Left Join not retrieving NULL value for the conditional field

I was trying to retrieve value from two tables by joining, but few values in the second columns missing.

eg

valuetable

----------------------
| id |orderid| prodid|
----------------------
|  1 |  NULL |      4|
----------------------
|  1 |     A |      4|
----------------------
|  2 |  NULL |      4|
----------------------
|  2 |  NULL |      3|
----------------------
|  3 |     B |      4|
----------------------
|  3 |     B |      3|
----------------------
|  3 |     B |      5|
----------------------

Nametable

--------------
| id |   Name|
--------------
|  1 |  Apple|
--------------
|  2 |   Ball|
--------------
|  3 |    Cat|
--------------
|  4 |    Dog|
--------------

I have the above two table. I have to retrieve values in the below format.

Query Result 

----------------------
| id |   Name|orderid|
----------------------
|  1 |  Apple|  NULL |
----------------------
|  2 |   Ball|  NULL |
----------------------
|  3 |    Cat|     B |
----------------------
|  4 |    Dog|  NULL |
----------------------

I have used the following query 

SELECT  nt.id, nt.[Name]
    , MIN(vt.orderid) AS orderid
FROM   Nametable nt  LEFT OUTER JOIN valuetable vt 
ON nt.id = vt.id
WHERE 
    vt.prodid = 3
GROUP BY
    nt.id, nt.[Name]

but missing the | 1 | Apple| NULL | row. How can I retrieve it? using MSSQL Server 2005

UPDATE

value table doesn't have id value 1 for prodid=3 but I need a result in the above mentioned way. means if it cannot get id from the valuetable for the corresponding prodid it should still retrieve the id and its name with NULL value as orderid.

Upvotes: 1

Views: 2118

Answers (3)

Bernhard Kircher
Bernhard Kircher

Reputation: 4182

the problem seems to be your where condition, since you perform a left join, vt.Prod_id may be null (and therefore is not 3).

If you want all Nametable entries (e.g. all names and null if the product is not mapped) for the product with id 3 (even there is no entry) you can try:

SELECT  nt.id, nt.[Name]
    , MIN(vt.orderid) AS orderid
FROM   Nametable nt  LEFT OUTER JOIN valuetable vt 
ON nt.id = vt.id
WHERE 
    (vt.prodid is null or vt.prodid = 3)
GROUP BY
    nt.id, nt.[Name]

Upvotes: 0

Brian Sayer
Brian Sayer

Reputation: 1

the null value will be ignored because you are using an aggregate function (MIN()). if you output results as text you should see this warning. also your sample query had vt.prodid = 3 where clause which obviously would exclude the row you're looking for

Upvotes: 0

Tejo
Tejo

Reputation: 557

Seems you have

WHERE vt.prodid = 3

valuetable does not contain an Apple with prodid = 3

So you should not have in the where but in the join clause:

SELECT  nt.id, nt.[Name]
, MIN(vt.orderid) AS orderid
FROM   Nametable nt  LEFT OUTER JOIN valuetable vt 
ON nt.id = vt.id AND vt.prodid = 3
GROUP BY
  nt.id, nt.[Name]

Upvotes: 3

Related Questions