Results 1 to 12 of 12

Thread: SQL Help

  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: SQL Help

    Hi,

    I have the following tables:

    Code:
    enquiries
    ------------------------------------------------------------------
    id | enquiry_type | interaction_id | party_id | enquiry_date
    ------------------------------------------------------------------
    1  |   ct billing |         1      |   10     | 2011-01-09 20:25:14
    2  |   ct spd     |         2      |   10     | 2011-01-10 10:00:00
    3  |   ct summon  |         2      |   10     | 2011-01-10 10:00:00
    4  |   ct faq     |         3      |    9     | 2011-01-10 10:10:00
    5  |   ct faq     |         4      |   NULL   | 2011-01-10 10:11:00
    -------------------------------------------------------------------
    
    party
    -----------------------
    party_add_id | party_id
    -----------------------
     1	     |	10
     2	     |	10
     3	     |	 9
    -----------------------
    
    party_address
    ------------------------------------------------------------------------------
    party_add_id|    address   |  start_date        | end_date           |prefered
    ------------------------------------------------------------------------------
    1           | 11 Green St  |2011-01-09 00:00:00 | 2011-01-10 09:59:00| N
    2           | 12 Blue  St  |2011-01-10 00:00:00 | NULL               | Y
    3           | 5 Pink St    |2011-01-09 00:00:00 | NULL               | Y
    ------------------------------------------------------------------------------
    I want a list of enquiries and the address the party was residing at whne the enquiry was made. I use the following SQL:

    Code:
    SELECT t1.enquiry_date
         , t1.enquiry_type
         , t1.interaction_id
         , t1.party_id
         , t3.address
    FROM enquiries t1
    LEFT JOIN (SELECT t1.party_id
                    , t2.address
    		, t2.start_date
    		, t2.end_date
    		, t2.prefered
                FROM party t1
                INNER JOIN party_address t2 on t1.party_add_id = t2.party_add_id) as t3 on t3.party_id = t1.party_id  
                 and ((t1.enquiry_date BETWEEN t3.start_date AND t3.end_date) OR (t3.end_date IS NULL AND t3.prefered = 'Y'))
    But i end up with duplicates as follows:

    Code:
    -----------------------------------------------------------------------------
    enquiry_date         |enquiry_type | interaction_id | party_id | address
    -----------------------------------------------------------------------------
    2011-01-09 20:25:14  |ct billing   |    1           |  10      |  11 Green St
    2011-01-09 20:25:14  |ct billing   |    1           |  10      |  12 Blue St
    2011-01-10 10:00:00  |ct spd       |    2           |  10      |  12 Blue St
    2011-01-10 10:00:00  |ct summon    |    2           |  10      |  12 Blue St
    2011-01-10 10:10:00  |ct faq       |    3           |   9      |  5 Pink St
    2011-01-10 10:11:00  |ct faq       |    4           |  Null    |    Null
    -----------------------------------------------------------------------------
    Why am I ending up with a duplicate? It should only return 5 records so how come am ending up with 6?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    duplicates are due to party 10 living at 2 addresses simultaneously that satisfy your WHERE clause

    by the way, since you require non-null values for t3 columns in your WHERE clause, it should be an INNER JOIN, not a LEFT OUTER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937 View Post
    duplicates are due to party 10 living at 2 addresses simultaneously that satisfy your WHERE clause

    by the way, since you require non-null values for t3 columns in your WHERE clause, it should be an INNER JOIN, not a LEFT OUTER JOIN
    OK let say I add the following records:

    Code:
    party
    -----------------------
    party_add_id | party_id
    -----------------------
     1	     |	10
     2	     |	10
     3	     |	 9
     4	     |	10
    -----------------------
    
    party_address
    ------------------------------------------------------------------------------
    party_add_id|    address   |  start_date        | end_date           |prefered
    ------------------------------------------------------------------------------
    1           | 11 Green St  |2011-01-09 00:00:00 | 2011-01-10 09:59:00| N
    2           | 12 Blue  St  |2011-01-10 10:00:00 | NULL               | Y
    3           | 5 Pink St    |2011-01-09 00:00:00 | NULL               | Y
    4           | 1 Red  St    |2011-01-09 00:00:00 | 2011-01-10 09:59:00| Y
    ------------------------------------------------------------------------------
    I use the following SQL:

    Code:
    SELECT t1.enquiry_date
         , t1.enquiry_type
         , t1.interaction_id
         , t1.party_id
         , t3.address
    FROM enquiries t1
    LEFT JOIN (SELECT t1.party_id
                    , t2.address
    		, t2.start_date
    		, t2.end_date
    		, t2.prefered
                FROM party t1
                INNER JOIN party_address t2 on t1.party_add_id = t2.party_add_id) as t3 on t3.party_id = t1.party_id  
                 and ((t1.enquiry_date BETWEEN t3.start_date AND t3.end_date AND t3.prefered = 'Y') 
    				 OR (t3.end_date IS NULL AND t3.prefered = 'Y'))
    I still end up with duplicates:

    Code:
    -----------------------------------------------------------------------------
    enquiry_date         |enquiry_type | interaction_id | party_id | address
    -----------------------------------------------------------------------------
    2011-01-09 20:25:14  |ct billing   |    1           |  10      |  11 Green St
    2011-01-09 20:25:14  |ct billing   |    1           |  10      |  1 Red St
    2011-01-10 10:00:00  |ct spd       |    2           |  10      |  12 Blue St
    2011-01-10 10:00:00  |ct summons   |    2           |  10      |  12 Blue St
    2011-01-10 10:10:00  |ct faq       |    3           |   9      |  5 Pink St
    2011-01-10 10:11:00  |ct faq       |    4           |  Null    |    Null
    -----------------------------------------------------------------------------
    The date enquiry 1 was made it should only pull our 1 Red St.
    Last edited by ozzii; 01-10-11 at 18:21. Reason: error

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ozzii View Post
    Party 10 is only living at 1 address when enquiry 1 was made.
    not according to your join conditions

    by the way, i'm sorry, i apologize about the WHERE clause and the INNER JOIN ...

    you have no WHERE clause

    however, your join conditions, marked in red here, allow address 2 to match enquiry date 2011-01-09 20:25:14
    Code:
      FROM enquiries t1
    LEFT 
      JOIN ( SELECT ... ) as t3 
        on t3.party_id = t1.party_id  
       and (  (t1.enquiry_date BETWEEN t3.start_date AND t3.end_date) 
           OR (t3.end_date IS NULL AND t3.prefered = 'Y')
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937 View Post
    not according to your join conditions

    by the way, i'm sorry, i apologize about the WHERE clause and the INNER JOIN ...

    you have no WHERE clause

    however, your join conditions, marked in red here, allow address 2 to match enquiry date 2011-01-09 20:25:14
    Code:
      FROM enquiries t1
    LEFT 
      JOIN ( SELECT ... ) as t3 
        on t3.party_id = t1.party_id  
       and (  (t1.enquiry_date BETWEEN t3.start_date AND t3.end_date) 
           OR (t3.end_date IS NULL AND t3.prefered = 'Y')
           )
    How can you get this to work so that I get the address the person was residing at whne the enquiry was made?

  6. #6
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937 View Post
    not according to your join conditions

    by the way, i'm sorry, i apologize about the WHERE clause and the INNER JOIN ...

    you have no WHERE clause

    however, your join conditions, marked in red here, allow address 2 to match enquiry date 2011-01-09 20:25:14
    Code:
      FROM enquiries t1
    LEFT 
      JOIN ( SELECT ... ) as t3 
        on t3.party_id = t1.party_id  
       and (  (t1.enquiry_date BETWEEN t3.start_date AND t3.end_date) 
           OR (t3.end_date IS NULL AND t3.prefered = 'Y')
           )
    This seems to work.

    Code:
    SELECT t1.enquiry_date
         , t1.enquiry_type
    	  , t1.interaction_id
    	  , t1.party_id
    	  , t3.address
    FROM enquiries t1
    LEFT JOIN (SELECT t1.party_id
                    , t2.address
    					 , t2.start_date
    					 , t2.end_date
    					 , t2.prefered
                FROM party t1
                INNER JOIN party_address t2 on t1.party_add_id = t2.party_add_id) as t3 on t3.party_id = t1.party_id  
                 and CASE WHEN t3.end_date IS NULL THEN ((t1.enquiry_date BETWEEN t3.start_date AND now()) AND t3.prefered = 'Y') 
    				 ELSE  ((t1.enquiry_date BETWEEN t3.start_date AND t3.end_date) AND t3.prefered = 'Y') END

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ozzii View Post
    How can you get this to work so that...
    me? i would fix the second condition, the one after the OR, because that's the one that's giving you all the trouble

    by the way, what is the preferred column for?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937 View Post
    me? i would fix the second condition, the one after the OR, because that's the one that's giving you all the trouble

    by the way, what is the preferred column for?

    prefered is the prefered mailing address i.e. a party could own 2 address but you can only have one prefered mailing address. We can then use that as the main party address whne they have multiple live addresses.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, that makes sense

    by the way, please consider replacing the solution you arrived at --
    Code:
        ON t3.party_id = t1.party_id  
       AND CASE WHEN t3.end_date IS NULL 
                THEN ((t1.enquiry_date BETWEEN t3.start_date AND now()) AND t3.prefered = 'Y') 
                ELSE ((t1.enquiry_date BETWEEN t3.start_date AND t3.end_date) AND t3.prefered = 'Y') 
            END
    with this --
    Code:
        ON t3.party_id = t1.party_id  
       AND t1.enquiry_date BETWEEN t3.start_date 
                               AND COALESCE(t3.end_date,CURRENT_DATE)
       AND t3.prefered = 'Y'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937 View Post
    okay, that makes sense

    by the way, please consider replacing the solution you arrived at --
    Code:
        ON t3.party_id = t1.party_id  
       AND CASE WHEN t3.end_date IS NULL 
                THEN ((t1.enquiry_date BETWEEN t3.start_date AND now()) AND t3.prefered = 'Y') 
                ELSE ((t1.enquiry_date BETWEEN t3.start_date AND t3.end_date) AND t3.prefered = 'Y') 
            END
    with this --
    Code:
        ON t3.party_id = t1.party_id  
       AND t1.enquiry_date BETWEEN t3.start_date 
                               AND COALESCE(t3.end_date,CURRENT_DATE)
       AND t3.prefered = 'Y'
    I seem to get null values for the address using the above?

  11. #11
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by ozzii View Post
    I seem to get null values for the address using the above?
    I've swapped current_date for now() and it seems to work

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my bad -- use CURRENT_TIMESTAMP instead of CURRENT_DATE

    obviously the enquiries you tested occurred after midnight this morning
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •