Is this statement valid - 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 2000 Forums

Transact-SQL (2000)

Is this statement valid

Author

Topic

lpatterson
Starting Member

4 Posts

Posted - 2008-08-01 : 10:16:14

Hello,

I have the following script that I need to confirm is logically sound. When I run the script I get the expected results, but my fellow dba says that it isn't logical and that the correct results were just a fluke. He says that I cannot reference the co_projects table in the main and subquery. If indeed this script is valid, could someone tell me what type of query it is??

-- This script will find companies that may have erroneous data in the co_projects table
-- where the project_id does not match the industry_id in the company_industry table.
-- There may be more than one project_id per company, but one of them needs to match.

-- Find companies where the project_id does not match the industry_id.

select ci.company_id, cp.project_id, ci.industry_id
from company_industry ci, co_projects cp
where ci.company_id = cp.company_id
and ci.in_book = 'Y'
and ci.industry_id not in (select cp.project_id
from co_projects cp
where ci.company_id = cp.company_id)
order by ci.company_id

r937
Posting Yak Master

112 Posts

Posted - 2008-08-01 : 11:40:30

no, you cannot reference in the SELECT clause a column in the NOT IN subquery

rudy
http://r937.com/

r937
Posting Yak Master

112 Posts

Posted - 2008-08-01 : 11:42:12

oops, i got it backwards

the subquery cannot use the same table alias inside the subquery scope as one used in the outer query

... or something like that

try renaming the table alias inside the subquery

rudy
http://r937.com/

lpatterson
Starting Member

4 Posts

Posted - 2008-08-01 : 11:42:13

Thank you!

quote:
Originally posted by r937

no, you cannot reference in the SELECT clause a column in the NOT IN subquery

rudy
http://r937.com/

lpatterson
Starting Member

4 Posts

Posted - 2008-08-01 : 11:59:46

So if I change the alias in the subquery only, it should work. Do I change just the pc alias or both pc and ci? When I change pc to cp it works, when I change both it returns 0.

select ci.company_id, cp.project_id, ci.industry_id
from company_industry ci, co_projects cp
where ci.company_id = cp.company_id
and ci.in_book = 'Y'
and ci.industry_id not in (select cp.project_id
from co_projects cp
where ci.company_id = cp.company_id)
order by ci.company_id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-01 : 13:16:56

it seems like you're trying to do this

select ci.company_id, cp.project_id, ci.industry_id
from company_industry ci, co_projects cp
where ci.company_id = cp.company_id
and ci.in_book = 'Y'
and ci.industry_id <>  cp.project_id

lpatterson
Starting Member

4 Posts

Posted - 2008-08-04 : 09:16:44

Thanks for responding. That is what I am trying to do. But the problem is in the co_projects table there could be more than one project_id but there should be at least one that matches the industry_id in the company_industry table. When I use "where project_id <> industry_id I don't get all the information I need. I need to know which ids match and which ones don't. That's why I wrote the first script which appeared to give me what I needed, but my associate says it's incorrect. I need to confirm the correct way to get the results I need or prove it to my associate that my script is correct too.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-04 : 13:59:13

quote:
Originally posted by lpatterson

Thanks for responding. That is what I am trying to do. But the problem is in the co_projects table there could be more than one project_id but there should be at least one that matches the industry_id in the company_industry table. When I use "where project_id <> industry_id I don't get all the information I need. I need to know which ids match and which ones don't. That's why I wrote the first script which appeared to give me what I needed, but my associate says it's incorrect. I need to confirm the correct way to get the results I need or prove it to my associate that my script is correct too.

Thanks

so what should be your desired output? can you explain with some sample data>?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources