help to write query .pls - 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 to write query .pls

Author

Topic

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-04-02 : 07:09:47

declare @Customer table
(cid int,
item varchar(50))
insert into @Customer values (1,'mobile')
insert into @Customer values (1,'TV')
insert into @Customer values(1,'Comp')
insert into @Customer values(1,'Laptop')
insert into @Customer values(2,'mobile')
insert into @Customer values(2,'TV')
insert into @Customer values(3,'Comp')
insert into @Customer values(3,'Laptop')

select * from @Customer

now i want to select the cid who bought mobile but not laptop
how do i write query for this

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-02 : 07:12:37

select * from @Customer where item in ('mobile')

select * from @Customer where item like 'mobile'

select * from @Customer where item not in ('Laptop')

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-04-02 : 07:12:50

select cid from @Customer
where item= 'mobile' and cid not in (Select cid from @Customer where item='laptop')

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-04-02 : 07:34:22

Hai,
thanks for the reply..but it doenst work for my scenario..
im sorry , icannot post my original code..
but the scenario is very similar,
i have a customer, who have tv,cable,internet.
so, when i say, get customer, who has cable but not tv, it returns me wrong result.
is there any way, further imporve the same same query which u sent me

quote:
Originally posted by darkdusky

select cid from @Customer
where item= 'mobile' and cid not in (Select cid from @Customer where item='laptop')

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-02 : 07:52:30

I dont think this is the best way but it is getting the desired output.

select * from
(
select * from @Customer where (item in('mobile') )
)t where t.cid not in (select cid from @Customer where item='laptop')

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-04-02 : 08:53:24

Actully in my scnerio, there are 9 standard items, each customer can have eiteher all the nine or less than nine..
im trying to generate the report. in which it try to find for one item, which that customer really doent have any record..as it is stored in row, im not able to use not in condition..
tooo poor

quote:
Originally posted by ayamas

I dont think this is the best way but it is getting the desired output.

select * from
(
select * from @Customer where (item in('mobile') )
)t where t.cid not in (select cid from @Customer where item='laptop')

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-04-02 : 09:29:56

quote:
Originally posted by darkdusky

select cid from @Customer
where item= 'mobile' and cid not in (Select cid from @Customer where item='laptop')

The query above will give all customers who have "a" but also do not have "b".

If this is not working in your case perhaps you can explain the logic you need.

Post your original code but put it into notepad and use Find / Replace to change the names of columns and tables if you are worried about security. E.g Table1, Col_1, Col_2, Col_3.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources