Duplicate Values

[tbianchi]

I'm trying to do a SELECT query to return specific results. Here's what I'm looking for.

In a table, I have stored values in two feilds (CustomerID) and (ItemId). I need to write a query that returns both customer id's and the item id IF there are the same item for two customers.

In addition, I'm looking to only find specific instances with two specific customers (customer #1 and customer #9).

Any ideas?

[MAK]

If it is sql server
-------------------

create table mytable1 (CustomerID int, ItemId int)
go
insert into mytable1 select 1,2
insert into mytable1 select 2,1
insert into mytable1 select 2,3
insert into mytable1 select 2,4
insert into mytable1 select 3,5
insert into mytable1 select 1,1
insert into mytable1 select 1,1
insert into mytable1 select 2,2
insert into mytable1 select 1,5
insert into mytable1 select 1,1
insert into mytable1 select 1,5
go


select customerid,itemid from mytable1
where itemid in (select distinct itemid from mytable1 where customerid in(1,9))
order by customerid,itemid

[Claire]

Try this


create table t6(Cust int,item int)

insert into t6 select 1,200
insert into t6 select 1,201
insert into t6 select 1,202
insert into t6 select 2,200
insert into t6 select 2,201
insert into t6 select 2,202
insert into t6 select 9,204
insert into t6 select 9,205
insert into t6 select 9,206
insert into t6 select 6,204
insert into t6 select 6,205

select * from t6
inner join (select * from t6 where cust = 1 or cust = 9)as a on t6.item = a.item and t6.cust <> a.cust

[tmls]

Hello,

try this:

select * from your_table where ItemId in (
select ItemId from your_table group by ItemId having count(CustomerID) > 1
) and (CustomerID = 1 or CustomerID = 9)


i think it works

bye