Why is this FULL JOIN only returning one row.

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

SQL Server 2005 Forums

Transact-SQL (2005)

Why is this FULL JOIN only returning one row.

Author

Topic

Perogy
Starting Member

7 Posts

Posted - 2010-09-13 : 15:55:43

Maybe I am not totally understanding the concept of a full join but I was under the impression that this should return 3 rows, not 1



SELECT * FROM
ms_Greenend T1
FULL OUTER JOIN ms_Strapper_Product T4 ON T4.Shiftnum = T1.Shiftnum AND T4.Product = T1.ProductNumber
WHERE T1.Shiftnum = 40430.0101

There is one row in ms_greenend and three rows in ms_Strapper_Product for that shiftnum. One of the ProductNumbers in ms_Strapper_Product matches the ProductNumber in ms_Greenend. It is only returning one row (the one where the productnumber matches). Shouldn't it return three rows? it seems more like it is doing an inner join than a full join.

Edit: Here this might help to see it better


ms_Greenend
Shiftnum      ProductNumber
40430.0101    183

ms_Strapper_Product
Shiftnum      ProductNumber
40430.0101    183
40430.0101    162
40430.0101    144

returns
Shiftnum      ProductNumber
40430.0101    183

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-13 : 16:17:39

You need to remove ProductNumber from the join condition:

SELECT * FROM
ms_Greenend T1
FULL JOIN ms_Strapper_Product T4 ON T4.Shiftnum = T1.Shiftnum
WHERE T1.Shiftnum = 40430.0101

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Perogy
Starting Member

7 Posts

Posted - 2010-09-13 : 16:50:29

Isn't a full join supposed to return the values that don't match as well? For example that should return Productnumbers 183,162, and 144. If i remove the productnumber from the join condition that query will return 3 rows but it is not the correct logic I am looking for. What if the product numbers in ms_greenend are completely different than the ones in ms_strapper_product? In that case i want it so I get one row for every product number, but it doesn't seem to happen this way

Sachin.Nand

2937 Posts

Posted - 2010-09-14 : 00:55:34

quote:
Originally posted by Perogy

Isn't a full join supposed to return the values that don't match as well? For example that should return Productnumbers 183,162, and 144. If i remove the productnumber from the join condition that query will return 3 rows but it is not the correct logic I am looking for. What if the product numbers in ms_greenend are completely different than the ones in ms_strapper_product? In that case i want it so I get one row for every product number, but it doesn't seem to happen this way

Change your query to this & try


SELECT * FROM
ms_Greenend T1
FULL OUTER JOIN ms_Strapper_Product T4 ON T4.Shiftnum = T1.Shiftnum AND T4.Product = T1.ProductNumber
AND T1.Shiftnum = 40430.0101

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Perogy
Starting Member

7 Posts

Posted - 2010-09-14 : 11:25:40

quote:
Originally posted by Idera

quote:
Originally posted by Perogy

Isn't a full join supposed to return the values that don't match as well? For example that should return Productnumbers 183,162, and 144. If i remove the productnumber from the join condition that query will return 3 rows but it is not the correct logic I am looking for. What if the product numbers in ms_greenend are completely different than the ones in ms_strapper_product? In that case i want it so I get one row for every product number, but it doesn't seem to happen this way

Change your query to this & try

SELECT * FROM
ms_Greenend T1
FULL OUTER JOIN ms_Strapper_Product T4 ON T4.Shiftnum = T1.Shiftnum AND T4.Product = T1.ProductNumber
AND T1.Shiftnum = 40430.0101

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

This seems to return the data for all the shifts. Not just 40430.0101. Thanks for the help everyone, I'll keep trying to figure this out

Perogy
Starting Member

7 Posts

Posted - 2010-09-14 : 11:30:08

I just found somewhere online that using a WHERE condition in a full outer join basically makes it into an inner join. So Idera I think you are on the right track with that change, but i still can't figure out how to get it to work

EDIT: If I completely take out the WHERE it works as I would like it to, but of course it shows all shifts. How can I filter this for shiftnum if I can't use a where?

Kristen
Test

22859 Posts

Posted - 2010-09-14 : 11:36:53

Haven't read the thread, but if you want some condition on a FULL OUTER JOIN you could use something like:

WHERE (T1.Shiftnum = 40430.0101 OR T1.Shiftnum IS NULL)

which will include where T1.Shiftnum has the value you want, or if it is NULL - i.e. no row present.

If T1.Shiftnum is not a required column then use one of the Primary Key columns instead:

WHERE (T1.Shiftnum = 40430.0101 OR T1.MyPkCol IS NULL)

otherwise you will get the Value you want, all rows from T2 that do not match T1, AND any row from T1 where Shiftnum happens to be NULL

Perogy
Starting Member

7 Posts

Posted - 2010-09-14 : 11:37:59

Well I figured it out,

It should be like this:


SELECT * FROM
ms_Greenend T1
FULL OUTER JOIN ms_Strapper_Product T4 ON T4.Shiftnum = T1.Shiftnum AND T4.Product = T1.ProductNumber
WHERE T4.Shiftnum = 40430.0101

I had to put the where condition on the table that I was joining onto, not on the original table. Strange.

Kristen
Test

22859 Posts

Posted - 2010-09-14 : 11:40:21

So you want all rows from T1 [ms_Greenend] and only rows from T4 [ms_Strapper_Product] that have Shiftnum = 40430.0101 ?

Kristen
Test

22859 Posts

Posted - 2010-09-14 : 11:43:29

Nope, I've got that wrong. Your query is NOT a Full Outer Join. Your query is:

All rows from T4 [ms_Strapper_Product] which have Shiftnum = 40430.0101 and any row from T1 [ms_Greenend] that matches on Shiftnum & ProductNumber

That would be the same as


SELECT * 
FROM ms_Strapper_Product T4
    LEFT OUTER JOIN ms_Greenend T1
         ON T1.Shiftnum = T4.Shiftnum
        AND T1.ProductNumber = T4.Product
WHERE T4.Shiftnum = 40430.0101

Perogy
Starting Member

7 Posts

Posted - 2010-09-14 : 11:49:38

Yes you are right, on further testing it is still not working correctly because it does not show the value in the left table if it is non matching.

What I want is non-matching and matching rows from the left side AND the right side for the specified shiftnum. I will play with the IS NULL thing a bit and see what I come up with

Perogy
Starting Member

7 Posts

Posted - 2010-09-14 : 11:57:20

So far this seems to be working for me.


SELECT * FROM
ms_Greenend T1
FULL OUTER JOIN ms_Strapper_Product T4 ON T4.Shiftnum = T1.Shiftnum AND T4.Product = T1.ProductNumber
WHERE T4.Shiftnum = 40430.0101 OR T4.Shiftnum IS NULL
AND T1.Shiftnum = 40430.0101

Maybe you guys can confirm and tell me if there are any problems with that.

Thanks for all the help

Kristen
Test

22859 Posts

Posted - 2010-09-15 : 04:26:25

WHERE T4.Shiftnum = 40430.0101 OR T4.Shiftnum IS NULL
AND T1.Shiftnum = 40430.0101

Personally I would use parenthesis where both AND and OR are used as I can never remember the precedence but, more importantly, it is all too easy to add more conditions that are then ambiguously interpreted

dcosta
Starting Member

1 Post

Posted - 2011-07-18 : 20:02:39

Hello!

After creating two temporary tables, when I select thenm with a full outer join zero lines are returned.

drop table #ms_Greenend
drop table #ms_Strapper_Product

create table #ms_Greenend
(Shiftnum decimal(8,3),
ProductNumber int);
--
create table #ms_Strapper_Product
(Shiftnum decimal(8,3),
ProductNumber int);
--
insert into #ms_Greenend values(40430.0101, 183);
--
insert into #ms_Strapper_Product values(40430.0101, 183);
insert into #ms_Strapper_Product values(40430.0101, 162);
insert into #ms_Strapper_Product values(40430.0101, 144);
--
select * from #ms_Greenend
select * from #ms_Strapper_Product
--
SELECT *
FROM #ms_Greenend as T1
FULL OUTER JOIN #ms_Strapper_Product as T4
ON T4.Shiftnum = T1.Shiftnum AND T4.ProductNumber = T1.ProductNumber
WHERE T1.Shiftnum = 40430.0101

Even when I retire the second condition "AND T4.ProductNumber = T1.ProductNumber" no line is returned

SELECT *
FROM #ms_Greenend as T1
FULL OUTER JOIN #ms_Strapper_Product as T4
ON T4.Shiftnum = T1.Shiftnum --AND T4.ProductNumber = T1.ProductNumber
WHERE T1.Shiftnum = 40430.0101

The environment is Windows XP
SQL Server 2005 Express

Thanks in advance for Your help
Dias Costa

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources