Left outer join 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

SQL Server 2005 Forums

Transact-SQL (2005)

Left outer join question!!

Author

Topic

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-10-19 : 18:58:30

Hi,

I have 2 tables:

Table 1:
ProductID
orderID
Description

Table2:
ProdID
ordID

select *
from dbo.table1
left outer join dbo.table2 on ProdID = ProductID

Is the left outer join correct? I want 2 things.

1) I want is that if the orderID is null in table1 then fill it with values from table2 if it exists.
2) so basically update orderId with value from table2 if the value of orderId is null or empty in table1

SA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-19 : 19:02:19

What happens if orderID is null in Table2? Do you need an outer join? Why can't you use an inner join instead?

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-10-19 : 19:22:23

I want all records from table2 into table1 based on the following condition:
If the Table2.orderId is null => replace it with table1.ordID

I am not sure if it should be inner or outer? Please advice

SA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-19 : 19:31:24

select table1.*
from dbo.table1
join dbo.table2
on ProdID = ProductID

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-10-19 : 20:02:37

Thank you Tara! One more question related to these 2 tables itself.

Table 1: (new master table)
ProductID
orderID
Description

Table2: (Legacy table - old data which needs to be transferred to the new table)
ProdID
ordID

So I need to check table2:ProdID and ordID against table1:ProductID and orderID. If these values do not exists in table1 (ie new master table) then insert a new row with this value else do nothing.

Thanks again.

SA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-19 : 20:12:47

INSERT INTO Table1 (ProductID, orderID)
SELECT ProdID, ordID
FROM Table2 t2
WHERE NOT EXISTS (SELECT * FROM Table1 t1 WHERE t1.ProductID = t2.ProdID AND t1.orderID = t2.ordID)

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-10-19 : 20:14:36

Let me test it out. Thanks for your help!!

SA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-19 : 20:15:13

You're welcome.

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-10-20 : 17:27:39

The query listed below works well but there is one more question. I also need to check if the t1.ProductID = t2.ProdID AND t1.TransID = t2.ordID. Please note that orderID and TransID is mapped to ordID in the TABLE 2. How would I perform this complicated query. There could be some overlap. THe total rows is only 1386 but I get 418 rows from first insert and 992 rows from the second insert. There is an overlap of 24 rows. Is this how you do this with 2 inserts or is there another approach to combine these 2 queries.

Table 1: (new master table)
ProductID
orderID -- orderID and TransId is mapped to ordID in the TABLE 2.
TransID
Description

Table2: (Legacy table - old data which needs to be transferred to the new table)
ProdID
ordID

-- inserts 418 rows
INSERT INTO Table1 (ProductID, orderID)
SELECT ProdID, ordID
FROM Table2 t2
WHERE NOT EXISTS (SELECT * FROM Table1 t1 WHERE t1.ProductID = t2.ProdID AND t1.orderID = t2.ordID)

--inserts 992 rows
INSERT INTO Table1 (ProductID, TransID)
SELECT ProdID, ordID
FROM Table2 t2
WHERE NOT EXISTS (SELECT * FROM Table1 t1 WHERE t1.ProductID = t2.ProdID AND t1.TransID = t2.ordID)

SA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-20 : 18:24:33

Maybe this:

INSERT INTO Table1 (ProductID, TransID)
SELECT ProdID, ordID
FROM Table2 t2
WHERE NOT EXISTS (SELECT * FROM Table1 t1 WHERE t1.ProductID = t2.ProdID AND (t1.orderID = t2.ordID OR t1.TransID = t2.ordID))

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-10-20 : 19:07:43

I tried this query and it gives me 24 rows ie the overlap rows as suggested in my question

SA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources