Help with SQL - 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)

Help with SQL

Author

Topic

s.wiley7
Starting Member

4 Posts

Posted - 2009-02-24 : 10:57:30

Someone else wrote a similar query and I was trying to figure out why they setup the subselect using table1 with a column from another table. Can someone explain?

SELECT COUNT(*)
FROM table1 AS D
WHERE D.customer_cd NOT IN (SELECT C.customer_id
FROM table1 AS A
LEFT JOIN table2 AS B ON B.main_num = A.main_num
LEFT JOIN table3 AS C ON B.other_num = C.other_num
AND B.cust_acct = C.cust_acct

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-24 : 12:21:32

quote:
Originally posted by s.wiley7

Someone else wrote a similar query and I was trying to figure out why they setup the subselect using table1 with a column from another table. Can someone explain?

SELECT COUNT(*)
FROM table1 AS D
WHERE D.customer_cd NOT IN (SELECT C.customer_id
FROM table1 AS A
LEFT JOIN table2 AS B ON B.main_num = A.main_num
LEFT JOIN table3 AS C ON B.other_num = C.other_num
AND B.cust_acct = C.cust_acct

This might produce wrong result.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-25 : 00:51:31

can u explain clearly about ur requirement with some sample data & output

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-02-25 : 01:19:11

Above query won't work as the subquery must be having some NULL values because of LEFT JOIN condition.
NOT IN always gives no result whenver subquery contains NULL values.
Anyway doing NOT IN is not a good idea, instead try this



SELECT COUNT(*) 
FROM table1 AS D LEFT JOIN 
(SELECT C.customer_id 
FROM table1 AS A 
LEFT JOIN table2 AS B ON B.main_num = A.main_num 
LEFT JOIN table3 AS C ON B.other_num = C.other_num
AND B.cust_acct = C.cust_acct) S
ON D.customer_id = S. customer_id
WHERE S. customer_id IS NULL

I also can't understand why are you doing LEFT JOIN inside subquery and having a column from LEFT table in select statement.

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-25 : 08:19:41

quote:
Originally posted by s.wiley7

Someone else wrote a similar query and I was trying to figure out why they setup the subselect using table1 with a column from another table. Can someone explain?

SELECT COUNT(*)
FROM table1 AS D
WHERE D.customer_cd NOT IN (SELECT C.customer_id
FROM table1 AS A
LEFT JOIN table2 AS B ON B.main_num = A.main_num
LEFT JOIN table3 AS C ON B.other_num = C.other_num
AND B.cust_acct = C.cust_acct

Actually query doesn't make sense.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources