How I use SQL command for this case

Yii 1.1 General Discussion
1

Table name "relation"




AID      BID

-------  -------

3        1

3        2

8        1

8        2

8        3

10       2

24       1

24       3

33       1

  • if set BID=1 , will got output AID = 3 , 8 , 24 , 33

  • if set BID=1 and BID=2 , will got output AID = 3 , 8

  • if set BID=1 and BID=3 , will got output AID = 8 , 24

  • if set BID=1 and BID=2 and BID=3 , will got output AID = 8

How I use SQL command for this case ?

2

Wouldn’t that output AID = 8 and 24 ?

3

Oh sorry, I’m mistake

4

So the sql would be something like:


SELECT * FROM relation r1 left join relation r2 on r1.aid=r2.aid where r1.bid=1 and r2.bid=3
5

Wow, Thank you but Have you other way to advice because If I set BID 10 values, I must join table with it self 10 times too.

6

There is another way:


SELECT aid FROM relation WHERE bid=1 or bid=2 or bid=3 group by aid having count(aid)=3

You just need to manualy give a number to the having statement, that number is the number of BID values you are searching…

7

Work! Thank you very much.