Subset Problem.....

dadalushhai · #1 (**permalink**) 11-23-05, 07:12

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.

andrewst · #2 (**permalink**) 11-23-05, 09:12

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;

madafaka · #3 (**permalink**) 11-23-05, 09:17

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

dadalushhai · #4 (**permalink**) 11-24-05, 08:01

Thanks to both of you... after a bit of modification to your queries I got my desired result... thanks again...

Internet Services

Web Development

Digital Marketing

Consumer Tech