join statement - 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

SQL Server 2005 Forums

Transact-SQL (2005)

join statement

Author

Topic

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-09-04 : 13:36:53

I need to have 3 table joins. Here's the question:

1. I need all the ordering codes in both the tables. Will this query work. Their are some in table1 and some in table 2 and some in both. But table2 has most of it.

2 I need to based on b.ProductId get the Product name from a 3rd table table3

I am a newbie and this is complicated for me.

Thanks,

Here's what I have so far:
______________________________________________________
select b.ProductId, a.col1, b.col2
from dbo.table1 a
right join dbo.table2 b
on a.ProductId = b.ProductId
order by a. ProductId

SA

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-04 : 13:58:09

Its hard to tell without seeing some sample data and expected output.

But I think you may need a FULL OUTER JOIN between table a and table b since you want all the rows from both.

Something like this perhaps...

select a.col1, b.col2,coalesce(c.ProductName,'')
from dbo.table1 a 
full outer join dbo.table2 b on a.ProductId = b.ProductId
inner join dbo.table3 c on b.ProductId = c.ProductId
order by b.ProductId

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-09-04 : 14:26:21

The table1 has 783 records
table2 has 11139
and this query returns 16968 records

Also I noticed that there are some a. ProductId that do not exists in table2 and even with a full outer join do not show in the result set.

Does this appear correct?

SA

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-09-04 : 14:37:49

What's not making sense to me is that its a full join. But there is productID ONLY in table1 which does not show up in the result set? How come? It is not there in table2

SA

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-09-04 : 15:06:06

select a.col1, b.col2,coalesce(c.ProductName,'')
from dbo.table1 a
full outer join dbo.table2 b on a.ProductId = b.ProductId
inner join dbo.table3 c on b.ProductId = c.ProductId
order by b.ProductId

Ignore earlier posts!! My question Full outer join in this situation should show the productID which is in table1 (ie a) and not in table2 (ie b). But its not doing that here How Come?

SA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources