Query help, common rows, simple?

ducati321 · #1 (**permalink**) 02-01-12, 11:18

Hi everyone, what I am trying to do I think is simple.

given a table of unique DomainId's:

domainids:
45
46
47
48
49
50
51

and given a table of domainId's tied to products (1 product, may have many domain id's)
productid domainid
-------------------------
1 45
1 46
2 47
3 49
3 50
3 51

Given a domainid (say 46), I want to select from my first table of domain ids, filtering out domainids that are NOT tied to more than one product with the given domainid.

in this example, given 46... I look at the second table, and see 46 and 45 are tied together. so when I select from my first table, 45 is the only one that is selected.

given 50, only 49 and 51 are selected from the first table.

hope that's not confusing.. i think it makes sense.

thanks for the assistance!

kordirko · #2 (**permalink**) 02-01-12, 15:38

Hi Ducati,

try this query

Code:

DECLARE @given_domid int;
set @given_domid = 50;

with domainids as (
select 45 domid union all
select 46 union all
select 47 union all
select 48 union all
select 49 union all
select 50 union all
select 51
), domainids_tied_to_products as (
select 1  prodid , 45 domid union all
select 1, 46 union all
select 2, 47 union all
select 3, 49 union all
select 3, 50 union all
select 3, 51
)
SELECT * 
FROM domainids
WHERE domid IN (
	select d2.domid
	from domainids_tied_to_products d1
	join domainids_tied_to_products d2
        on  d1.prodid = d2.prodid 
	where d1.domid = @given_domid
          and d2.domid <> d1.domid
)
;

Internet Services

Web Development

Digital Marketing

Consumer Tech