1. Registered User
Join Date
Nov 2005
Posts
2

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 08:51. Reason: Further explaination...

2. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
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. Registered User
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. Registered User
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
•