LEFT JOIN Results question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

LEFT JOIN Results question

Author

Topic

siumui
Yak Posting Veteran

54 Posts

Posted - 2012-05-09 : 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

354 Posts

Posted - 2012-05-09 : 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

52326 Posts

Posted - 2012-05-09 : 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

15 Posts

Posted - 2012-05-09 : 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
*/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources