| Author |
 Topic  |
|
|
siumui
Starting Member
48 Posts |
 Posted - 05/09/2012 : 11:03:24
|
Hello all.
I have two tables:
Table 1 contains 2 columns: account (unique), date
Table 2 contains 3 columns: account, purchase_date, item_type
Let's say table 1 contains 2 rows with the following data:
account date
1000 12/30/2011
2000 11/20/2011
Let's say table 2 contains 4 rows with the following data:
account purchase_date item_type
1000 1/1/2012 Shirt
2000 2/1/2012 Jean
2000 2/9/2012 Shoes
2000 2/15/2012 Hat
I wrote a query to practice below:
SELECT a.account, date, purchase_date, item_type
FROM Table1 a
LEFT JOIN Table2 b
ON a.account = b.account
WHERE item_type IN ('Shirt', 'Jean', 'Shoes', 'Hat')
AND purchase_date > date
The query returned rows like below:
account date purchase_date item_type
1000 12/30/2011 1/1/2012 Shirt
2000 11/20/2011 2/1/2012 Jean
2000 11/20/2011 2/9/2012 Shoes
2000 11/20/2011 2/15/2012 Hat
2000 11/20/2011 2/1/2012 Jean
2000 11/20/2011 2/9/2012 Shoes
2000 11/20/2011 2/15/2012 Hat
2000 11/20/2011 2/1/2012 Jean
2000 11/20/2011 2/9/2012 Shoes
2000 11/20/2011 2/15/2012 Hat
Can someone please tell me why the account 2000 returned 9 times? Shouldn't it return only 3 times for there are only 3 records for account 2000 in Table2? I'm very confused. How do I work around this? Please help.
Thank you.
|
|
|
vijays3
Constraint Violating Yak Guru
India
311 Posts |
Posted - 05/09/2012 : 11:19:46
|
Check once again. I create the same tables as yours ,It does not give 9 records..Check once again at your end.
create table #table1 (account int unique, [date] varchar(20))
insert into #table1(account,[date])
select 1000,'12/30/2011'
union all
select 2000, '11/20/2011'
create table #table2(account int, purchase_date varchar(20), item_type varchar(20
))
insert into #table2(account,purchase_date,item_type)
select 1000 ,'1/1/2012', 'Shirt'
union all
select 2000,'2/1/2012','Jean'
union all
select 2000,'2/9/2012','Shoes'
union all
select 2000,'2/15/2012','Hat'
select * from #table1
select * from #table2
SELECT a.account, date, purchase_date, item_type
FROM #Table1 a
LEFT JOIN #table2 b
ON a.account = b.account
WHERE item_type IN ('Shirt', 'Jean', 'Shoes', 'Hat')
AND purchase_date > date
Result is:account date purchase_date item_type
2000 11/20/2011 2/1/2012 Jean
2000 11/20/2011 2/9/2012 Shoes
2000 11/20/2011 2/15/2012 Hat |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 05/09/2012 : 16:01:25
|
I'm sure you've not given us big picture...I think you've some other columns also involved in join which was not shown above
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
RL
Starting Member
USA
15 Posts |
Posted - 05/09/2012 : 18:03:08
|
With the tables and rows you provided, the most you could EVER get is 8 rows from a CROSS JOIN (4 x 2):
SELECT a.account, a.[date], b.purchase_date, b.item_type
FROM Table1 a CROSS JOIN Table2 b
/*
account [date] purchase_date item_type
1000 2011-12-30 2012-01-01 Shirt
1000 2011-12-30 2012-02-01 Jean
1000 2011-12-30 2012-02-09 Shoes
1000 2011-12-30 2012-02-15 Hat
2000 2011-11-20 2012-01-01 Shirt
2000 2011-11-20 2012-02-01 Jean
2000 2011-11-20 2012-02-09 Shoes
2000 2011-11-20 2012-02-15 Hat
*/
One thing that would produce your results would be if the query joined to Table2 TWICE:
SELECT a.account, a.[date], c.purchase_date, c.item_type
FROM Table1 a LEFT JOIN Table2 b ON a.account = b.account
LEFT JOIN Table2 c ON c.account = b.account
WHERE b.item_type IN ('Shirt', 'Jean', 'Shoes', 'Hat')
AND b.purchase_date > a.[date];
/*
account [date] purchase_date item_type
1000 2011-12-30 2012-01-01 Shirt
2000 2011-11-20 2012-02-01 Jean
2000 2011-11-20 2012-02-09 Shoes
2000 2011-11-20 2012-02-15 Hat
2000 2011-11-20 2012-02-01 Jean
2000 2011-11-20 2012-02-09 Shoes
2000 2011-11-20 2012-02-15 Hat
2000 2011-11-20 2012-02-01 Jean
2000 2011-11-20 2012-02-09 Shoes
2000 2011-11-20 2012-02-15 Hat
*/
Now let's go back to the original query with Table1 and Table2:
The way it's written, the original query will act like an INNER JOIN, no matter what data is in the tables. This is because filters on Table2 columns (OUTER table columns) appear in the WHERE clause rather than the JOIN predicate (ON clause).
NOTE: In order to test for unmatched results in the LEFT JOIN, I added a row to Table1 with account=3000, [date]='2011-10-01'. The original query doesn't return the unmatched row with account=3000, but the second query below it does.
-- same as INNER JOIN
SELECT a.account, a.[date], b.purchase_date, b.item_type
FROM Table1 a LEFT JOIN Table2 b
ON a.account = b.account
WHERE b.item_type IN ('Shirt', 'Jean', 'Shoes', 'Hat')
AND b.purchase_date > a.[date];
/*
account [date] purchase_date item_type
1000 2011-12-30 2012-01-01 Shirt
2000 2011-11-20 2012-02-01 Jean
2000 2011-11-20 2012-02-09 Shoes
2000 2011-11-20 2012-02-15 Hat
*/
-- now it works
SELECT a.account, a.[date], b.purchase_date, b.item_type
FROM Table1 a LEFT JOIN Table2 b
ON a.account = b.account
AND b.item_type IN ('Shirt', 'Jean', 'Shoes', 'Hat')
AND b.purchase_date > a.[date];
/*
account [date] purchase_date item_type
1000 2011-12-30 2012-01-01 Shirt
2000 2011-11-20 2012-02-01 Jean
2000 2011-11-20 2012-02-09 Shoes
2000 2011-11-20 2012-02-15 Hat
3000 2011-10-01 NULL NULL
*/
|
|
|
| |
Topic |
|
|
|
LEFT JOIN Results question
