#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    35
    Rep Power
    2

    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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Left Join
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    35
    Rep Power
    2

    duh...


    thanks man!

IMN logo majestic logo threadwatch logo seochat tools logo