SQL query - Return 0 for the column if no record found-CodePudding

I will like to see the specific fields returns with 0 value when there is no record found. Below are my SQL queries that I have tried so far.

SELECT Customer, Address, Number of lines, Date 
FROM table_name 
WHERE Date = '30-5-2022' AND Customer IN (A, B, C)

What it returns is only 1 row as below.

Customer	Address	Number of Lines	Date
A	3	RF	30-5-2022

But what I expected to see is:

Customer	Address	Number of Lines	Date
A	UK	33	30-5-2022
B	0	0	30-5-2022
C	0	0	30-5-2022

The customer B and C has no record on 30-5-2022 but I will still need to see the rows but certain columns can be 0. Please advise if anything that I have missed out? Thanks a lot!

CodePudding user response：

Assuming customers are in one table (cust) and other stuff in another table (table_name) and they are connected with an id called custid, you need some sweet, sweet left join action, such as:

SELECT c.Customer, c.Address, isnull([Number of lines],0), d.Date 
FROM cust c left join  table_name d on c.custid = d.custid and 
d.Date = '30-5-2022' where 
c.Customer IN ('A', 'B', 'C')

CodePudding user response：

You need to put A, B, C in a table and left-join the main table to it. All other conditions must then go in the ON clause not the WHERE.

You can use a virtual VALUES clause for this

SELECT Customer, Address, Number of lines, Date 
FROM (VALUES
 ('A'),
 ('B'),
 ('C')
) v(Customer)
LEFT JOIN table_name tn ON tn.Customer = v.Customer
                        AND tn.Date = '30-5-2022';

Alternatively you can pass in a Table Valued Parameter, or use a table variable. Don't forget to add a primary key.

DECLARE @tmp TABLE (Customer varchar(100) PRIMARY KEY);
INSERT @tmp(Customer) VALUES
 ('A'),
 ('B'),
 ('C');

SELECT Customer, Address, Number of lines, Date 
FROM @tmp v
LEFT JOIN table_name tn ON tn.Customer = v.Customer
                        AND tn.Date = '30-5-2022';

CodePudding user response：

Try below query:

SELECT A.Customer, ISNULL(B.Address, 0), 
ISNULL(B.[Number of lines],0), ISNULL(B.Date, '30-05-2022') Date
FROM
(
 SELECT DISTINCT Customer 
 FROM table_name
) A
LEFT JOIN table_name B
ON A.Customer = B.Customer
AND B.Date = '30-5-2022'

This will output all the customers present in the table. You can filter the customers with WHERE clause in the end of the above query based on your requirement.

dbfiddle Link