1

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 ?

LuckyLuke82
  • 586
  • 1
  • 18
  • 58

1 Answers1

3

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
             );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Could a DISTINCT help with duplicates? I didn't tested the query, but i guess something like this might do the job without adding another select: SELECT DISTINCT t2.* FROM ... – Jacopo Dec 07 '16 at 16:32
  • @Gordon Linoff, thanks. I tested both of them and they worked. But I need a little explanation...I allready did a simple JOIN as you did before posting and output was same. Your suggestion differs only here "SELECT t2.*" - what I did was simply "SELECT * from". Is that a bug or what ?...Take a look at my edited query in question, you'll see there. – LuckyLuke82 Dec 07 '16 at 16:37
  • @Jacopo, problem was only that beside my related records also many-to-many fields are displayed in results. – LuckyLuke82 Dec 07 '16 at 16:39
  • yes, and for this Gordon's solution to use TABLE2.* in the select will surely solve the issue. i was just wondering if the second query (to get rid of duplicates) could have been simplified – Jacopo Dec 07 '16 at 16:41
  • @Jacopo, can you explain why does It come to this bug ? I mean If you're simply doing SELECT * from ? – LuckyLuke82 Dec 07 '16 at 16:44
  • When you do a JOIN (or INNER JOIN, or LEFT JOIN, or RIGHT JOIN) your result set will be composed of the union of both tables columns (all columns from TABLE2 and from TABLE3 in your case), if you only want to select the columns of one of the tables you can do that as Gordon wrote, with TABLENAME.* ( "*" stands for "all". "all columns of TABLENAME). (see also http://stackoverflow.com/questions/1329662/select-only-some-columns-from-a-table-on-a-left-join) – Jacopo Dec 07 '16 at 16:50
  • @Jacopo, ok, but this still isn't an answer on why this bug with "SELECT * from" happens. Except answer of VoteyDisciple, If you meant that. – LuckyLuke82 Dec 07 '16 at 18:22
  • it is not a bug, it's the correct behaviour of a join – Jacopo Dec 07 '16 at 19:30