CODEWITHSUNDEEP
sqloracle-databaseselect
LuckyLuke82
LuckyLuke82

Reputation: 606

Getting all related data from many-to-many table

This is my select to obtain all data of Table2 from M-M table. All I have to obtain data from tables is ID_TABLE1_FK=3 value.

    Select s1.* from TABLE2 s1 
    JOIN TABLE3 s2 ON s2.ID_TABLE2_FK = s1.ID_TABLE2_PK
    WHERE s2.ID_TABLE1_FK=3;

   'this query returns me data from table1 same way - I need to combine
   'them both in 1 row, that is desired output
    Select a1.* from TABLE1 a1 
    JOIN TABLE3 s2 ON s2.ID_TABLE1_FK = a1.ID_TABLE1_PK
    WHERE s2.ID_TABLE1_FK=3;

Now I want to obtain all data from Table1 as well, in the same query. How?

These are my table designs:

    M-M table:
    ID_TABLE3_PK
    ID_TABLE1_FK
    ID_TABLE2_FK

    TABLE1:
    ID_TABLE1_PK
    Name
    Surname

    TABLE2:
    ID_TABLE2_PK
    Street
    Address

DESIRED OUTPUT (with only ID_TABLE1_FK=3):

Name,Surname,Street,Address

Upvotes: 1

Views: 85

Answers (1)

Mureinik
Mureinik

Reputation: 311893

You can join both table1 and table2 on table3:

SELECT s1.*, a1.* 
FROM   TABLE2 s1 
JOIN   TABLE3 s2 ON s2.ID_TABLE2_FK = s1.ID_TABLE2_PK
JOIN   TABLE1 a1 ON s2.ID_TABLE1_FK = a1.ID_TABLE1_PK
WHERE  s2.ID_TABLE1_FK=3;

Upvotes: 2

Related Questions