0

I have two queries having same columns, 1 returning 3 rows and other returning 2 rows. While I join this two query I am able to get only 2 rows. How can I get three rows with not matching record as null in sql server?

Query1:

Select UDA.production_date_T, Count (WO.order_state) AS 'cntplan' 
From WORK_ORDER WO 
INNER JOIN UDA_Order UDA ON UDA.object_key = WO.order_key 
group by UDA.production_date_T) TBL1

Query2:

Select UDA.production_date_T, Count (WO.order_state) AS 'ACTUAL' 
From WORK_ORDER WO 
INNER JOIN UDA_Order UDA 
ON UDA.object_key = WO.order_key 
WHERE WO.order_state = 'BOOKED' 
GROUP BY UDA.production_date_T

My desired Output is:

production_date_T           cntplan     ACTUAL
2019-04-26                       22         4
2019-05-03                        4        NULL
2019-09-05                        19        0
5
  • 1
    Hi and welcome to DBA.SE! Do you by any chance also have the table definitions and some sample data? May 20, 2019 at 19:46
  • Hi @RandiVertongen, The sample data is: table 1 returns output : production_date_T cntplan 2019-04-26 22 2019-05-03 4 2019-09-05 19 and my query2 returns output: production_date_T ACTUAL 2019-04-26 4 2019-09-05 6
    – SHALINI
    May 20, 2019 at 19:48
  • Have you considered using LEFT JOIN instead of INNER JOIN?
    – Queue Mann
    May 20, 2019 at 19:57
  • Please provide sample data as DML - also table data as DDL. p.s. welcome to the forum! 8-) May 20, 2019 at 20:02
  • 1
    Please edit your question when adding details. It's hard reading DDL or data from the comments.
    – John K. N.
    May 21, 2019 at 5:42

1 Answer 1

1

Seems to me like you just need to move the filter you are using in the second query as a CASE expression:

SELECT  UDA.production_date_T, 
        COUNT(WO.order_state) cntplan,
        SUM(CASE WHEN WO.order_state = 'BOOKED' THEN 1 ELSE 0 END) ACTUAL
FROM dbo.WORK_ORDER WO 
INNER JOIN dbo.UDA_Order UDA 
    ON UDA.object_key = WO.order_key 
GROUP BY UDA.production_date_T
;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.