CODEWITHSUNDEEP
sqljoin
BringQBasicBack
BringQBasicBack

Reputation: 119

SQL left join returns nothing when no matches

I am writing a stored procedure that adds the counts to two fields. I have the following code:

    SELECT Distinct DateTime1,SUM(TICKETREQ1)SUMREQ, SUM(TicketPU1)SUMPU1, (count(*))AS GRADCOUNT
    FROM TABLEA
    WHERE YEAR = '2015'
        AND TicketReq1 > 0
        group by DateTime1

    Select DISTINCT(DateTime2),SUM(TicketReq2) SUMREQ,SUM(TicketPU2)SUMPU2, (count(*))AS GRADCOUNT
    from TABLEA
    where TicketReq2 > 0
        and YEAR = '2015'
        Group by DateTime2;


SELECT Distinct c.DateTime1,SUM(c.TICKETREQ1 + b.TicketReq2)SUMREQ, SUM(c.TicketPU1 + b.TicketPU2)SUMPU1, (count(b.id) + count(c.id))AS GRADCOUNT
FROM TABLEA c
LEFT JOIN TABLEA b 
    ON (b.DateTime2 = c.DateTime1
    AND b.TicketReq2 > 0
    AND b.YEAR = '2015')
WHERE c.YEAR = '2015'
AND c.TicketReq1 > 0
group by c.DateTime1

This returns:
enter image description here

For some ceremonies the second query does bring in results and adds them correctly. But if there are no records then it fails.

enter image description here


How can I get it to join the two counts together (Query 1 and 2) so that Query 3 displays both counts even when there is no match

Upvotes: 1

Views: 74

Answers (2)

sfuqua
sfuqua

Reputation: 5863

The problem is the SUM statements on query #3. b.TicketReq2 is null, therefore SUM(c.TICKETREQ1 + b.TicketReq2) should encounter an error. Try using ISNULL(b.TicketReq2, 0) in your SUM function calls.

Upvotes: 1

Synodica Solutions Pvt. Ltd.
Synodica Solutions Pvt. Ltd.

Reputation: 1315

Try full outer join instead of left join

SELECT Distinct c.CeremonyDateTime1,SUM(c.TICKETREQ1 + b.TicketReq2)SUMREQ, SUM(c.TicketPU1 + b.TicketPU2)SUMPU1, (count(b.gid) + count(c.gid))AS GRADCOUNT
FROM ComTicket c
FULL OUTER JOIN ComTicket b 
ON (b.CeremonyDateTime2 = c.CeremonyDateTime1
AND b.TicketReq2 > 0
AND b.Gradterm = '201540')
WHERE c.gradterm = '201540'
AND c.TicketReq1 > 0
group by c.CeremonyDateTime1

It might helpful to you..

Upvotes: 0

Related Questions