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

    Join Date
    Oct 2012
    Posts
    35
    Rep Power
    2

    Weird JOIN procedure - right track?


    I have a customer table that has 4 different ac columns. I use a union because I need to display each customer record as many times as an ac record exists for each customer.

    This has worked just fine for me until now.

    I now have another table called xferleads which may have hundreds of records per customer.

    Each time the query below returns a record I would like it also to add a column for the LATEST xferleads.timestamp. Problem is with a join, it does as many records as there are timestamps in xferleads where I only want one per customer with ac - the latest one.

    I can't figure out how to use LIMIT or if it even would apply here.

    Example: customer 115 has 4 ac columns that aren't NULL. the return should be 4 rows with the last column being the latest timestamp from xferleads. if no timestamp exists, null.

    Code:
    SELECT 
    	customers.customer
    	, customers.ac0 AS ac
    	, customers.xfer_no2
    	, customers.state
            , customers.carrier
    	, customers.lead_type
    	, customers.criteria
    	, xferleads.timestamp
    FROM customers
    JOIN xferleads ON xferleads.customer = customers.id 
    WHERE customers.ac0 IS NOT NULL AND pri='NORMAL' AND customers.ac0 > 1 
    
    
    UNION SELECT 
    	customers.customer
    	, customers.ac1 
    	, customers.xfer_no2
    	, customers.state
    	, customers.carrier
    	, customers.lead_type
    	, customers.criteria
    	, xferleads.timestamp
    FROM customers
    JOIN xferleads ON xferleads.customer = customers.id
    WHERE customers.ac1 IS NOT NULL AND pri='NORMAL' AND customers.ac1 > 1 
    
    
    UNION SELECT 
    	customers.customer
    	, customers.ac2 
    	, customers.xfer_no2
    	, customers.state
    	, customers.carrier
    	, customers.lead_type
    	, customers.criteria
    	, xferleads.timestamp
    FROM customers 
    JOIN xferleads ON xferleads.customer = customers.id
    WHERE customers.ac2 IS NOT NULL AND pri='NORMAL' AND customers.ac2 > 1 
    
    
    UNION SELECT 
    	customers.customer
    	, customers.ac3 
    	, customers.xfer_no2
    	, customers.state
    	, customers.carrier
    	, customers.lead_type
    	, customers.criteria
    	, xferleads.timestamp
    FROM customers  
    JOIN xferleads ON xferleads.customer = customers.id
    WHERE customers.ac3 IS NOT NULL AND pri='NORMAL' AND customers.ac3 > 1 
    
    
    UNION SELECT 
    	customers.customer
    	, customers.ac4 
    	, customers.xfer_no2
    	, customers.state
    	, customers.carrier
    	, customers.lead_type
    	, customers.criteria
    	, xferleads.timestamp
    FROM customers 
    JOIN xferleads ON xferleads.customer = customers.id
     WHERE customers.ac4 IS NOT NULL AND pri='NORMAL' AND customers.ac4 > 1 
    
    
    ORDER BY ac
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    You have an odd database design with the ac0-ac4 that really would need some normalization so that you don't have to write such a large UNION (which you by the way probably should change to UNION ALL since you say you want all rows from the inner SELECT's and a UNION ALL makes it more explicit and faster than a UNION that always tried to perform a DISTINCT on the resulting table).


    But what you want to use is GROUP BY with a MAX() on
    timestamp to get the latest timestamp:
    Code:
    SELECT 
    	customers.customer
    	, customers.ac0 AS ac
    	, customers.xfer_no2
    	, customers.state
            , customers.carrier
    	, customers.lead_type
    	, customers.criteria
    	, MAX(xferleads.timestamp) AS maxTimeStamp
    FROM customers
    JOIN xferleads ON xferleads.customer = customers.id 
    WHERE customers.ac0 IS NOT NULL AND pri='NORMAL' AND customers.ac0 > 1 
    GROUP
      BY
        	customers.customer
    	, customers.ac0
    	, customers.xfer_no2
    	, customers.state
            , customers.carrier
    	, customers.lead_type
    	, customers.criteria
    
    ...
    /Stefan
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    35
    Rep Power
    2

    still can't get it to work


    told you it was weird.
    just returns the one customer with the latest timestamp instead of each customer with latest timestamp

    agreed that db needs some alterations and normalization but i just need to get this working for now and i'll circle back around later.

    Code:
     SELECT 
    	customers.customer
    	, customers.ac0 AS ac
    	, customers.xfer_no2
    	, customers.state
            , customers.carrier
    	, customers.lead_type
    	, customers.criteria
    	, MAX(xferleads.timestamp) AS maxTimeStamp
    FROM customers
    JOIN xferleads ON xferleads.customer = customers.id 
    WHERE customers.ac0 IS NOT NULL AND pri='NORMAL' AND customers.ac0 > 1 
    
    
    UNION ALL SELECT 
    	customers.customer
    	, customers.ac1
    	, customers.xfer_no2
    	, customers.state
    	, customers.carrier
    	, customers.lead_type
    	, customers.criteria
    	, MAX(xferleads.timestamp) AS maxTimeStamp
    FROM customers
    JOIN xferleads ON xferleads.customer = customers.id
    WHERE customers.ac1 IS NOT NULL AND pri='NORMAL' AND customers.ac1 > 1 
    
    
    UNION ALL SELECT 
    	customers.customer
    	, customers.ac2
    	, customers.xfer_no2
    	, customers.state
    	, customers.carrier
    	, customers.lead_type
    	, customers.criteria
    	, MAX(xferleads.timestamp) AS maxTimeStamp
    FROM customers 
    JOIN xferleads ON xferleads.customer = customers.id
    WHERE customers.ac2 IS NOT NULL AND pri='NORMAL' AND customers.ac2 > 1 
    
    
    UNION ALL SELECT 
    	customers.customer
    	, customers.ac3 
    	, customers.xfer_no2
    	, customers.state
    	, customers.carrier
    	, customers.lead_type
    	, customers.criteria
    	, MAX(xferleads.timestamp) AS maxTimeStamp
    FROM customers  
    JOIN xferleads ON xferleads.customer = customers.id
    WHERE customers.ac3 IS NOT NULL AND pri='NORMAL' AND customers.ac3 > 1 
    
    
    UNION ALL SELECT 
    	customers.customer
    	, customers.ac4
    	, customers.xfer_no2
    	, customers.state
    	, customers.carrier
    	, customers.lead_type
    	, customers.criteria
    	, MAX(xferleads.timestamp) AS maxTimeStamp
    FROM customers 
    JOIN xferleads ON xferleads.customer = customers.id
     WHERE customers.ac4 IS NOT NULL AND pri='NORMAL' AND customers.ac4 > 1 
    
    GROUP BY
    customers.customer
    	, customers.ac0
    	, customers.xfer_no2
    	, customers.state
            , customers.carrier
    	, customers.lead_type
    	, customers.criteria
    
    ORDER BY ac
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    you forgot the GROUP BY clauses
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    35
    Rep Power
    2

    got it...but...


    forgot to mention...
    need to also return records if xferleads.timestamp doesn't exist as null.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,927
    Rep Power
    378
    Would it help if 'customer' and 'ac' were two separate tables?

IMN logo majestic logo threadwatch logo seochat tools logo