CODEWITHSUNDEEP
sqloracle-database
LuckyLuke82
LuckyLuke82

Reputation: 606

Many-To-Many - get all records from related record

This is my many-to-many table:

Table3:

ID_TABLE3
ID_TABLE1_FK
ID_TABLE2_FK
Some_Field

Now what I want is to do a select of all records from TABLE2 where ID_TABLE1_FK in TABLE3 = 3. This is my query, and It returns all records, but It adds all fields of TABLE3 at end - WHICH IS NOT DESIRED !! :

SELECT * from TABLE2
JOIN TABLE3 ON TABLE3.ID_TABLE2_FK = TABLE2.ID_TABLE2
WHERE TABLE3.ID_TABLE1_FK= 3

So where am I wrong ?

Upvotes: 1

Views: 56

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

Just use a regular JOIN and select the columns you really want;

SELECT t2.* 
FROM TABLE2 t2 JOIN
     TABLE3 t3
     ON t3.ID_TABLE2_FK = t2.ID_TABLE2
WHERE t3.ID_TABLE1_FK = 3;

This could conceivably produce duplicates (if they are in TABLE3). So, you might be better off with:

SELECT t2.*
FROM TABLE2 t2
WHERE EXISTS (SELECT 1
              FROM TABLE3 t3
              WHERE t3.ID_TABLE2_FK = t2.ID_TABLE2 AND t3.ID_TABLE1_FK = 3
             );

Upvotes: 3

Related Questions