avoiding subselect query - 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

General SQL Server Forums

New to SQL Server Programming

avoiding subselect query

Author

Topic

yumyum113
Starting Member

31 Posts

Posted - 2007-03-02 : 19:11:57

Hi,

Hope someone could help me in revising a long running query. Here is the query

select *
from table1
where classid is null
and productid not in (
select productid
from table1
where classid = 67)

In here table1 could have several occurance of productid in which productid could have different classid. The possible values of classid are: NULL,1,2,3,67. Basically I am looking for all records whose classid is null but should never had an instance in table1 where its classid is 67.

Do you have something like a "join" statment that will only include all records in the left table that is not in the right table?

Hope someone could help me with this. Thanks in advance.

-Ruel

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-02 : 19:20:31

[code]
select *
from table1 t1 left join table1 t2
on t1.productid = t2.productid
and t1.classid is null
and t2.classid = 67
where t2.productid is null
[/code]

yumyum113
Starting Member

31 Posts

Posted - 2007-03-02 : 19:51:17

Hi KH,

Thanks for your reply. But when I use the query that you posted I did not get the result that I was looking for. I had test statement when I test it...

create table #table1
(
productid int,
classid int
)

insert into #table1 values(5295037,NULL)
insert into #table1 values(5295037,67)

insert into #table1 values(5303304,NULL)
insert into #table1 values(5303304,67)

insert into #table1 values(5307114,NULL)
insert into #table1 values(5307114,67)

insert into #table1 values(5307128,NULL)

insert into #table1 values(5307222,NULL)

select *
from #table1 t1 left join #table1 t2
on t1.productid = t2.productid
and t1.classid is null
and t2.classid = 67
where t1.productid is null

Here is the result that I got...

ProductID CLASSID PRODUCTID CLASSID
5295037 67 NULL NULL
5303304 67 NULL NULL
5307114 67 NULL NULL
5307128 NULL NULL NULL
5307222 NULL NULL NULL

Thanks anyway...

-Ruel

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-03-03 : 04:37:10

do you want to exclude rows that have classid=67?
from your sample, you only have one table... am i missing something?

select * from table1
where classid<>67

or

select * from table1 where classid is null (since you have the AND operator)

or was it a typo, 2 diff tables actually?

--------------------
keeping it simple...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-03 : 20:52:09

quote:
But when I use the query that you posted I did not get the result that I was looking for.

So what is your expected result ?

yumyum113
Starting Member

31 Posts

Posted - 2007-03-03 : 22:40:51

The result that I am looking for based on the 3rd reply are productID

5307128
5307222

Because they both have NULL classID and they don't have a classID equal to 67.

-Ruel

yumyum113
Starting Member

31 Posts

Posted - 2007-03-03 : 23:03:17

Hi Jen,

The query really involves just one table. Using the temporary table from reply number 3, and also the records inserted in it. I am looking for a query that will return all records with productID that has NULL classID but these productIDs should never had any instances where its classID is equal to 67. The query should not use a subselect query as what is in the original post, by the way the script in the original post should bring up the right result. But is there anyway to have the same result without using a subselect query, cause the subselect causes the script run too slow.

-Ruel

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-04 : 00:21:08

[code]
select *
from #table1 t1 left join #table1 t2
on t1.productid = t2.productid
and t1.classid is null
and t2.classid = 67
where t2.productid is null
and t1.classid is null
[/code]

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-03-04 : 05:42:51

if this is 2005

select productid from #table1
except
select productid from #table1
where classid=67

--------------------
keeping it simple...

yumyum113
Starting Member

31 Posts

Posted - 2007-03-04 : 12:18:13

Hi KH,

Thanks for the solution, this problem have been on my mind since last thursday.

Hi Jen,

Actually im running it on SQL server 2000, but when I tried running it on SQL 2005 your solution also works... "Except" hmm.. an interesting keyword i'll read more about it later. Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources