CODEWITHSUNDEEP
phpmysql
codeispoetry
codeispoetry

Reputation: 373

mysql left join not return all left table row

I have 2 tables 

phonetype:
phonetypeID (PK)
phonetyepe

phone:
phoneID (PK)
peopleID (fK)
phonetypeID (fK)
areacode
number`

For a specific peopleID I would like to return all the phonetypes and if the peopleID has the number diplay it, if not empty. Something like:

phonetype    phonetypeID    areacode    number
company      1              111         11111
fax          2
home         3              222         222222
mobile       4

Here is my prepared statement:

$stmt = $conn->prepare("SELECT *
FROM phonetype
LEFT JOIN phone
ON phonetype.phonetypeID=phone.phonetypeID
where phone.peopleID = ?; 
");
if ( !$stmt ) {die(printf("Error: %s.\n", mysqli_stmt_error($stmt) ) );}
else if ( !$stmt->bind_param('i', $peopleID) ) {die(printf("Error: %s.\n", mysqli_stmt_error($stmt) ) );}

But this way I only have

phonetype    phonetypeID    areacode    number
company      1              111         11111
home         3              222         222222

What am I doing wrong? PS this is my first join!! Thanks!!

EDIT (This question follows this other question [display data from database in a specific order: php or mysql? . After reading the answers of Gravel, Robbie Averill, Maximus2012 and Miken32 I understood that it was a better design to have a dedicate table phonetype, and use a foreign key in the phone table. Thinking that this was a completely different scenario, I asked this new question. If anybody thinks this is a duplicate and there is a way to join the 2 question, I will do it. Thanks again to everybody who took the time to help me.)

Upvotes: 0

Views: 621

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

In your query;

SELECT *
FROM phonetype
LEFT JOIN phone
ON phonetype.phonetypeID=phone.phonetypeID
where phone.peopleID = ?

...the WHERE clause with a condition on the rightmost table will remove all rows where the rightmost table has no value, negating the LEFT JOIN.

What you most likely want is to add the condition to the LEFT JOIN's ON clause instead, allowing the empty values of phone to still show up;

SELECT *
FROM phonetype
LEFT JOIN phone
  ON phonetype.phonetypeID=phone.phonetypeID
 AND phone.peopleID = ?

Upvotes: 5

SIDU
SIDU

Reputation: 2278

use AND instead of WHERE

SELECT *
FROM phonetype
LEFT JOIN phone
ON phonetype.phonetypeID=phone.phonetypeID
AND phone.peopleID = ?

Upvotes: 2

Related Questions