Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2010
    Posts
    18

    Unanswered: Query help, common rows, simple?

    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!

  2. #2
    Join Date
    Jan 2012
    Posts
    84
    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
    )
    ;

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •