joins to verify validity of IDs

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)

joins to verify validity of IDs

Author

Topic

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-10-20 : 20:16:10

A new question. I have 3 tables and a sql query. I need to check table2.OrdID against the table3.OrderingCode to make sure it is a valid code before I update table1. Please advice. I added left outer join. But the results give me the same # of rows even though there are a few invalid ids in table2. So the results does not seem correct.

Table 1: (new master table)
ProductID
orderID
Description

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

Table 3: (master table)
OrderingCode
desc

-- inserts 418 rows
INSERT INTO Table1 (ProductID, orderID)
SELECT ProdID, ordID
FROM Table2 t2
left outer join dbo.Table3 as a on a.OrderingCode=t2.ordID
WHERE NOT EXISTS (SELECT * FROM Table1 t1 WHERE t1.ProductID = t2.ProdID AND t1.orderID = t2.ordID)

SA

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-20 : 20:50:34

INSERT INTO Table1 (ProductID, orderID)
SELECT t2.ProdID, t2.ordID
FROM Table1 t1
LEFT JOIN Table2 t2
ON t1.ProductID = t2.ProdID AND t1.orderID = t2.ordID
WHERE isnull(t2.productId,t2.orderid) is null
and
t2.ordid in (select orderingCode from table3) ?

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-21 : 12:32:07

isnt it just a matter of

INSERT INTO Table1 (ProductID, orderID)
SELECT ProdID, ordID
FROM Table2 t2
inner join dbo.Table3 as a
on a.OrderingCode=t2.ordID

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-10-21 : 13:45:09

Vishak i tried your query but it does not work. It gives me 11 records that can't be correct. I am expecting at least 3000 plus records.

SA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-21 : 13:50:22

how many records does table 2 & table 3 have?

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-10-21 : 13:51:53

Ps: I am just using the table 3 to check the validity of ordID. Table3 is the master table and if Table2.ordID exists in Table3. It is valid and we want it in our resultset.

SA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-21 : 13:54:10

and if not present in t3 you want to ignore?

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-10-21 : 13:55:27

okay. table2 = 1386; table3= 4184 and the query below gives 405

select ordID from table2 where ordID in
(select distinct OrderingCode from table3)

SA

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-10-21 : 13:56:30

and if not present in t3 you want to ignore?

YES!!!

SA

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-10-21 : 14:10:41

Should it be an inner, outer, left join in the scenario for table3 . Just an inner join should work in the general case but using it in this query does not give the right # of records. Please advice!!

INSERT INTO Table1 (ProductID, orderID)
SELECT ProdID, ordID
FROM Table2 t2
inner join dbo.Table3 as a on a.OrderingCode=t2.ordID
WHERE NOT EXISTS (SELECT * FROM Table1 t1 WHERE t1.ProductID = t2.ProdID AND t1.orderID = t2.ordID)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources