Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2005
    Posts
    2

    Unanswered: Subset Problem.....

    hello,

    I have an associative table consisting of two columns namely "AId" and "BId". Both these columns are foriegn keys of two distinct other tables. Now, one AId can relate to multiple BIds and vice versa.

    I am given a particular AId and I can easily get it's corresponding BIds. Now the problem is that I want to have all the AIds that are also related to maximum of those BIds that I just got through that particular AId. Secondly, I want to have all those Bids that are related to the maximum of Aids that i just got above in decending order (most related first).

    What is the most suitable and efficient way to perform this?


    AId BId

    1 a
    1 b
    1 c

    2 c
    2 b
    2 e
    2 f
    2 g

    3 a
    3 e
    3 b
    3 f

    4 e
    4 g
    4 c
    4 i

    5 k
    5 l
    5 m

    If Aid = "1" is the targeted search then resulting Aids should be 2,3 because in "2" {c,b} are present and in "3" {a,b} and "4" {c} respecctively are present secondly the BId that we should recieve are {e,f,g} in descending order because "e" is asociated with maximum of the AIds that we got from above and then "f" and "g" because they are less occuring than "e" and so on....

    thanks.
    Last edited by dadalushhai; 11-23-05 at 09:51. Reason: Further explaination...

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If I understand you correctly:

    Code:
    select bid 
    from   t
    where  aid = (select aid 
                  from   t
                  where  bid = (select max(bid)
                                from   t 
                                where  aid=123
                               )
                 )
    order by bid desc;

  3. #3
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    It seems you have one input (Aid = 1) and 3 questions. Here could be how you get answers (if I understood correctly):

    Code:
    select Bid 
    from tmp 
    where Aid = 1
    Code:
    select distinct Aid
    from tmp 
    where Bid in (select Bid 
    		from tmp 
    		where Aid = 1
    		)
    and Aid != 1
    Code:
    select Bid from
    (
    select distinct Bid, count(*) cnt
    from tmp 
    where Aid in (select distinct Aid
    		from tmp 
    		where Bid in (select Bid 
    				from tmp 
    				where Aid = 1
    				)
    		and Aid != 1
    		)
    group by Bid
    ) x
    order by cnt desc

  4. #4
    Join Date
    Nov 2005
    Posts
    2
    Thanks to both of you... after a bit of modification to your queries I got my desired result... thanks again...

Posting Permissions

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