0

I have two tables in my DB:

Table A:

  • ID
  • P1
  • P2

Table B:

  • ID
  • P3
  • A_ID which is a foreign key for Table A.ID

Assuming I have the following data in my tables:

+----+----+----+           +----+----+------+
| ID | P1 | P2 |           | ID | P3 | A_ID |
+----+----+----+           +----+----+------+
| 1  | aa | aa |           | B1 | aa |  1   |
+----+----+----+           +----+----+------+
| 2  | bb | bb |
+----+----+----+

As you can see only the 1st entity of table A has a relationship with an entity of table B, so I want to show (SELECT) something like this and set to 2nd entity of table A NULL values:

+----+----+----+----+
| P1 | P2 | P3 | ID |
+----+----+----+----+
| aa | aa | aa | B1 |
+----+----+----+----+
| bb | bb |null|null|
+----+----+----+----+

Which is the query I need to write in order to achieve that result?

Thanks in advance for your reply.

  • You need an Outer Join: a LEFT join b – dnoeth Nov 10 at 10:42
  • @dnoeth I have tried it but probrably I am doing something wrong. May you show exactly how to do this ? – Riccardo Raffini Nov 10 at 10:43
  • Show your existing Select. You probably got a condition in the wrong place. Rule of thumb: conditions on the outer table (a) are added to WHERE, but conditions on the inner table (b) are added to ON, e.g. from a LEFT join b on a.col=b.col and b.col > 0 where a.col> 0 – dnoeth Nov 10 at 10:57
1

The relationship is not between individual rows but between tables.
A foreign key is the database way to enforce said relationship.

Having said that - you want a left join query:

SELECT A.P1, A.P2, B.P3, B.ID
FROM A
LEFT JOIN B
    ON A.ID = B.A_ID

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.