Weird join - PART II

Dev Shed Forums Sponsor:

October 15th, 2012, 01:49 PM

karnival800

Contributing User

Join Date: Oct 2012

Posts: 35

Time spent in forums: 4 h 15 m 8 sec
Reputation Power: 1

Weird join - PART II

I posted before on this and thanks to you guys it works great.
I forgot to mention however that I need SQL to return results even if no record exists in xferleads.

More help would be appreciated!

...and yes I know this is a weird db layout.

Code:

 
SELECT 
	customers.customer
	, customers.ac0 AS ac
	, customers.xfer_no2
	, customers.state
        , customers.carrier
	, customers.lead_type
	, customers.criteria
	, customers.pri
	, MAX(xferleads.timestamp) AS maxTimeStamp
FROM customers
JOIN xferleads ON xferleads.customer = customers.id 
WHERE customers.ac0 IS NOT NULL AND pri <> 'NONE' AND customers.ac0 > 1 

GROUP BY
customers.customer
	, customers.ac0
	, customers.xfer_no2
	, customers.state
        , customers.carrier
	, customers.lead_type
	, customers.criteria
	, customers.pri

UNION ALL SELECT 
	customers.customer
	, customers.ac1
	, customers.xfer_no2
	, customers.state
	, customers.carrier
	, customers.lead_type
	, customers.criteria
	, customers.pri
	, MAX(xferleads.timestamp) AS maxTimeStamp
FROM customers
JOIN xferleads ON xferleads.customer = customers.id
WHERE customers.ac1 IS NOT NULL AND pri <> 'NONE' AND customers.ac1 > 1 

GROUP BY
customers.customer
	, customers.ac0
	, customers.xfer_no2
	, customers.state
        , customers.carrier
	, customers.lead_type
	, customers.criteria
	, customers.pri
UNION ALL SELECT 
	customers.customer
	, customers.ac2
	, customers.xfer_no2
	, customers.state
	, customers.carrier
	, customers.lead_type
	, customers.criteria
	, customers.pri
	, MAX(xferleads.timestamp) AS maxTimeStamp
FROM customers 
JOIN xferleads ON xferleads.customer = customers.id
WHERE customers.ac2 IS NOT NULL AND pri <> 'NONE' AND customers.ac2 > 1 

GROUP BY
customers.customer
	, customers.ac0
	, customers.xfer_no2
	, customers.state
        , customers.carrier
	, customers.lead_type
	, customers.criteria
	, customers.pri
UNION ALL SELECT 
	customers.customer
	, customers.ac3 
	, customers.xfer_no2
	, customers.state
	, customers.carrier
	, customers.lead_type
	, customers.criteria
	, customers.pri
	, MAX(xferleads.timestamp) AS maxTimeStamp
FROM customers  
JOIN xferleads ON xferleads.customer = customers.id
WHERE customers.ac3 IS NOT NULL AND pri <> 'NONE' AND customers.ac3 > 1 

GROUP BY
customers.customer
	, customers.ac0
	, customers.xfer_no2
	, customers.state
        , customers.carrier
	, customers.lead_type
	, customers.criteria
	, customers.pri
UNION ALL SELECT 
	customers.customer
	, customers.ac4
	, customers.xfer_no2
	, customers.state
	, customers.carrier
	, customers.lead_type
	, customers.criteria
	, customers.pri
	, MAX(xferleads.timestamp) AS maxTimeStamp
FROM customers 
JOIN xferleads ON xferleads.customer = customers.id
 WHERE customers.ac4 IS NOT NULL AND pri <> 'NONE' AND customers.ac4 > 1 

GROUP BY
customers.customer
	, customers.ac0
	, customers.xfer_no2
	, customers.state
        , customers.carrier
	, customers.lead_type
	, customers.criteria
	, customers.pri

ORDER BY ac ASC, pri DESC, maxTimeStamp ASC