hELP TO write the 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

SQL Server 2005 Forums

Transact-SQL (2005)

hELP TO write the query

Author

Topic

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-04-06 : 22:27:37

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')

declare @custname table
(cid int,
cname varchar(20)
)
insert into @custname values (1,'custone')
insert into @custname values (2,'custtwo')
insert into @custname values (3,'custthree')

SELECT * FROM @Customer WHERE ITEM IN ('mobile','TV')

for the above query i just want to get only the cuseromer 2 inforamtion..b'coz that is the one matching the above criteria..but as, customer 1 having the same record along with 'Comp','Laptop'
it is returing both the records..
how do i eliminate it..
Help pls

micnie_2020
Posting Yak Master

232 Posts

Posted - 2009-04-06 : 22:45:31

Dear Sir,

Kindly look at this url:-
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=123260

Hope it's may help you.

Thank you.

Regards,
Michelle

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-04-06 : 23:03:18

Hai i know to concatenate the values, that i have done using the udf.
but my requirement, is how to use that in "WHERE" condition

quote:
Originally posted by micnie_2020

Dear Sir,

Kindly look at this url:-
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=123260

Hope it's may help you.

Thank you.

Regards,
Michelle

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-07 : 00:52:15

Hi,
Try this query

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 where cid in
(
SELECT count(cid) FROM @Customer WHERE ITEM IN ('mobile','TV')
group by cid
having count(cid)=2)

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-04-07 : 01:17:44

quote:
Originally posted by kunal.mehta

Hi,
Try this query

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 where cid in
(
SELECT count(cid) FROM @Customer WHERE ITEM IN ('mobile','TV')
group by cid
having count(cid)=2)

I THINK UR qry should be this right

select * from @customer where cid in
(
SELECT (cid) FROM @Customer WHERE ITEM IN ('mobile','TV')
group by cid
having count(cid)=2)?????

im not sure, is this correct. but i think it may work..
is there anyother stable solution for this?!?!?!

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-07 : 01:28:26

Hi,
This will work and u need to change the count value as per your selection.
e.g for 'mobile','tv' it is 2

I ll look for some solid solution
Kunal

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-04-07 : 02:06:25

declare @str varchar(25)
declare @NoItems int
set @str = 'ab,cd'
set @ItemsCount = len(@str) - len(replace(@str,',','')) + 1

select I.CustId from Items I
inner join (select CustID,count(*) as NoItems from Items group by CustId having count(*) = @ItemsCount) J on I.CustId = J.CustId
where charindex(','+ Item + ',',',' + @str + ',' ) > 0
group by I.CustId having count(*) = @ItemsCount

Sarakumar
Posting Yak Master

108 Posts

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

Hai Friends,
Tell me how stable the following solution will be
select * from @Customer a
where
exists (select 1 from @Customer c
where a.cid=c.cid
and c.item = 'mobile' )
and exists (select 1 from @Customer c
where a.cid=c.cid
and c.item = 'TV')

kunal.mehta
Yak Posting Veteran

83 Posts

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

Hi,
For case when i need to search for n items, I need to run n sub queries. eventually become very slow.
Kunal

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-04-07 : 03:05:50

im...that's draw back..but i have only 9 items. so believe shdnt be a problem..let me try to find something in the mean time

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-04-07 : 04:19:31

Use this stored procedure. pass the items in commaseperated format as input. it will retrieve the only customers that have these products only.

create proc sc_getITems
(
@str varchar(25)
)
as
begin
declare @ItemsCount int
set @str = 'ab,cd'
set @ItemsCount = len(@str) - len(replace(@str,',','')) + 1

select I.CustId from Items I
inner join (select CustID,count(*) as NoItems from Items group by CustId having count(*) = @ItemsCount) J on I.CustId = J.CustId
where charindex(','+ Item + ',',',' + @str + ',' ) > 0
group by I.CustId having count(*) = @ItemsCount
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources