CODEWITHSUNDEEP
mysqlsql
wayne9003
wayne9003

Reputation: 39

Combine and Display query result

I have 2 query:

SELECT CustomerID,count(b.BookingStatus) as 'NotComplete'
FROM Booking b, Customer c 
WHERE c.CustomerID=b.BookingCustomerID
AND(b.BookingStatus='Pending'
OR b.BookingStatus='OTW')
GROUP BY c.CustomerID


SELECT c.CustomerID, r.*
FROM Customer c,Regular r
WHERE c.CustomerID=r.RegularCID
Result:

1st query enter image description here

2nd queryenter image description here

How to combine these 2 result together?

also, display the zero(count) as well.

Thanks!

this is what I get after few hours of trying..obviously it's not what I want..

SELECT c.CustomerID,count(b.BookingStatus) as 'NotComplete',r.RegularID
FROM Booking b, Customer c 
JOIN Regular r on r.RegularCID=c.CustomerID
WHERE c.CustomerID=b.BookingCustomerID

AND (b.BookingStatus='Pending'
or b.BookingStatus='OTW'
or b.BookingStatus='Started'
or b.BookingStatus='Unclaimed'
or b.BookingStatus='Confirmed')
GROUP by r.RegularID

Upvotes: 0

Views: 38

Answers (2)

Nick
Nick

Reputation: 147146

You can JOIN to the Regular table and then LEFT JOIN to a derived table of counts in the Booking table. We do it this way to avoid having to GROUP BY all the columns in the Regular table:

SELECT c.CustomerID, r.*, b.NotComplete
FROM Customer c
JOIN Regular r ON r.RegularCID = c.CustomerID
LEFT JOIN (SELECT BookingCustomerID, COUNT(*) AS NotComplete
           FROM Booking
           WHERE BookingStatus IN ('Pending', 'OTW', 'Started', 'Unclaimed', 'Confirmed')
           GROUP BY BookingCustomerID) b ON b.BookingCustomerID = c.CustomerID

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13006

Use join on regular table, and subquery on your first select

SELECT t1.*, r.RegularCID FROM (
    SELECT CustomerID,count(b.BookingStatus) as 'NotComplete',
    FROM Booking b
    INNER JOIN Customer c ON c.CustomerID=b.BookingCustomerID
    WHERE (b.BookingStatus='Pending' OR b.BookingStatus='OTW')
    GROUP BY c.CustomerID) t1
LEFT JOIN Regular r on r.CustomerID = t1.CustomerID

Upvotes: 0

Related Questions