SQL join and get all data even not exist in main table-CodePudding

I need to get count for header. So I have table A which contain transaction records, and table B which contain status of the transaction. I want to accumulate the data to show table A result count based from table B. But currently I still cannot get the empty data too.

I have create a SQLFiddle

As you can see from the image above, status with TSID: 4 (Complete) is not listed with zero.

I have tried some script

SELECT 
COUNT(CASE WHEN A.Status = 0 THEN 1 END) AS Pending,
COUNT(CASE WHEN A.Status = 1 THEN 1 END) AS Assigned,
COUNT(CASE WHEN A.Status = 2 THEN 1 END) AS Started,
.
.
.
.

But I don't want this way because on client, I use a loop and render display based from the result return from SQL.

I have try LEFT JOIN, LEFT OUTER JOIN but not as expected result. I'm not much good with SQL join part, frankly speaking.

CodePudding user response：

You need a LEFT JOIN, but you must flip around the order of the tables

SELECT
  TS.TSID,
  TS.TSName,
  COUNT(T.Status) as Total
FROM B TS 
LEFT JOIN A T ON T.Status = TS.TSID 
GROUP BY
  TS.TSID,
  TS.TSName 
ORDER BY
  TS.TSID ASC;

SQL Fiddle

You can also do this as a RIGHT JOIN with the original order, however this can cause confusion especially in the presence of other joins. And because joins are commutative, you can just swap them around. This is why right joins are very rarely used in practice.

Note also that the grouping is over TS.TSID as T.Status may be null in some cases.

CodePudding user response：

When you want all the records from the second table in the join, even if there is no match in the first table you need RIGHT JOIN. I have also removed MAX(T.status) from ORDER BY because it does not produce the result you want.

SELECT TS.TSID,TS.TSName,
   COUNT(T.Status) as Total 
FROM A T 
RIGHT JOIN B TS 
ON Status=TS.TSID 
GROUP BY T.Status,
   TS.TSID,TS.TSName 
ORDER BY 
   MAX(TS.TSID) ASC;