| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
01-10-11, 16:15
|
|
Registered User
|
|
Join Date: Mar 2007
Posts: 194
|
|
|
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?
|
|
01-10-11, 16:44
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,535
|
|
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
|
|
01-10-11, 16:51
|
|
Registered User
|
|
Join Date: Mar 2007
Posts: 194
|
|
|
|
Quote:
Originally Posted by r937
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 17:21.
Reason: error
|
01-10-11, 17:12
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,535
|
|
Quote:
Originally Posted by ozzii
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')
)
|
|
01-10-11, 17:29
|
|
Registered User
|
|
Join Date: Mar 2007
Posts: 194
|
|
Quote:
Originally Posted by r937
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?
|
|
01-10-11, 17:56
|
|
Registered User
|
|
Join Date: Mar 2007
Posts: 194
|
|
Quote:
Originally Posted by r937
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
|
|
01-10-11, 17:58
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,535
|
|
Quote:
Originally Posted by ozzii
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?
|
|
01-10-11, 18:09
|
|
Registered User
|
|
Join Date: Mar 2007
Posts: 194
|
|
Quote:
Originally Posted by r937
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.
|
|
01-10-11, 18:14
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,535
|
|
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'
|
|
01-10-11, 18:26
|
|
Registered User
|
|
Join Date: Mar 2007
Posts: 194
|
|
Quote:
Originally Posted by r937
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?
|
|
01-10-11, 18:31
|
|
Registered User
|
|
Join Date: Mar 2007
Posts: 194
|
|
Quote:
Originally Posted by ozzii
I seem to get null values for the address using the above?
|
I've swapped current_date for now() and it seems to work 
|
|
01-10-11, 18:41
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,535
|
|
my bad -- use CURRENT_TIMESTAMP instead of CURRENT_DATE
obviously the enquiries you tested occurred after midnight this morning
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
SQL Help
