CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Sam Bin Ham
Sam Bin Ham

Reputation: 449

Return NULL value if no record found using joins

Below SQL query for return NULL value if there is no record returns using joins.

My query:

SELECT 
    ISNULL((SELECT SR_No
            FROM Product
            WHERE SR_No in ('8877','9911')), NULL)

Basically I need to check if the SR_No is available in the Product table.

How I can modify my script to get output like below:

Table -> Item_M: 

+-------+---------+
| SR_No | Asso_Id |
+-------+---------+
|  4455 |     100 |
|  4455 |     200 |
|  4455 |     300 |
|  8877 |     500 |
|  9911 |     600 |
+-------+---------+

Table->Product: 

| SR_No | Pro_N | Pro_Sc |
+-------+-------+--------+
|  4455 | SA    | S      |
|  8877 | BT    | B      |
|       |
+-------+-------+--------+

Expected output:

+-------+-------+--------+
| SR_No | Pro_N | Pro_Sc |
+-------+-------+--------+
| 8877  | BT    | B      |
| NULL  | NULL  | NULL   |
+-------+-------+--------+

Upvotes: 0

Views: 1704

Answers (2)

Michał Turczyn
Michał Turczyn

Reputation: 37367

Try this:

SELECT CASE WHEN SR_No IN ('8877','9911') THEN SR_No END AS SR_No,
       CASE WHEN SR_No IN ('8877','9911') THEN Pro_N END AS Pro_N,
       CASE WHEN SR_No IN ('8877','9911') THEN Pro_Sc END AS Pro_Sc
FROM Product

SQL Fiddle

You can modify it entering subquery in place of ('8877','9911').

Upvotes: 0

StuartLC
StuartLC

Reputation: 107247

This looks like a good fit for using a Table Valued Constructor to create a table of the items you are looking for, and then you can LEFT JOIN the Products (and possibly Items) table to it, in order to either selectively show the other columns, or NULL if the qualifying criteria aren't met:

-- If you want the SR_No number listed along with NULLs
SELECT x.SR_No, p.Pro_N, p.Pro_Sc
FROM (values (8877),(1234)) as x(SR_No)
LEFT OUTER JOIN Product p
on x.SR_No = p.SR_No;

Output:

SR_No   Pro_N   Pro_Sc
8877    BT      B
1234    (null)  (null)

And if you don't want to show the SR_No value alongside the nulls:

-- If you want the SR_No listed as NULL instead
SELECT p.SR_No, p.Pro_N, p.Pro_Sc
FROM (values (8877),(1234)) as x(SR_No)
LEFT OUTER JOIN Product p
on x.SR_No = p.SR_No;

Giving:

SR_No   Pro_N   Pro_Sc
8877    BT      B
(null)  (null)  (null)

The problem with the second approach is that you'll be showing multiple rows of NULL columns if there are multiple non-matches, which is unlikely to be useful to your code consuming this query.

If you need to further qualify that there must be Item rows present for the product as well then adjust the query:

LEFT OUTER JOIN Product p
on x.SR_No = p.SR_No AND EXISTS (SELECT 1 FROM Item_M i WHERE i.SR_No = p.SR_No);

SqlFiddle here

Upvotes: 2

Related Questions