Question 174777 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Get row from first table in many to many by two id from second table

i have a problem with sql request. I have 3 tables.
table_1

id name
1 aaa
2 bbb
3 ccc
4 ddd

table_2

id name
1 zzz
2 yyy
3 xxx
4 www

And third table for many to many relationship.

table_1_table_2

table_1_id table_2_id
1 1
1 2
3 3
4 4

In request i have 2 id from table 2.
And finally my question.
How to find row from table_1 which have relationship with two row in table_2?

SELECT * FROM `table_1`
JOIN `table_1_table_2` ON table_1.id=table_1_table_2.table_1_id
JOIN `table_2` ON table_2.id=table_1_table_2.table_2
WHERE table_2.id = 1 AND table_2.id = 2
Tags:

3

Answers


  1. 0

    try this:

    SELECT t1.*
FROM table1 t1
JOIN table2_table1 tt ON tt.table1_id = t1.id
JOIN table2 t2 ON tt.table2_id = t2.id
WHERE t2.id = 'second_table_id1'
  AND t2.id = 'second_table_id2';
    Login or Signup to reply.
  2. 0 
    WHERE table_2.id = 1 AND table_2.id = 2

    it is impossible to have a row with two different ids in the same column
    perhaps you meant:

    WHERE table_1.id = 1 AND table_2.id = 2
    Login or Signup to reply.
  3. 0

    You don’t need to join all tables. It’s sufficient to use the relationships table to count amount of id2 for each id1, then join with tab1 if you want to gather id1 names.

    SELECT tab1.id,
       tab1.name
FROM       tab1
INNER JOIN tab12 
        ON tab1.id = tab12.table_1_id
GROUP BY tab1.id,
         tab1.name
HAVING COUNT(tab12.table_2_id) = 2

    Output:

    id name
    1 aaa

    Check the demo here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search