mysql left join with nulls in php

Question

Example scenario; lets say I'm searching for employees and their employers

tblEmployee
eid | name
1  | james
2  | harry

tblCompany
cid | name
1  | ABC Ltd
2  | XYZ Corp

tblMappings
mid | eid | cid
1  | 1   | 2

James is assigned to the company XYZ Corp, but harry has no assignment. I want to pull out all employees whether they are assigned or not;

SELECT * FROM `tblEmployee`
LEFT JOIN `tblMappings` ON 
`tblEmployee`.`eid` = `tblMappings`.`eid`

This returns the following in phpMyAdmin;

eid | name  | mid  | eid  | cid
1   | james | 1    | 1    | 2
2   | harry | NULL | NULL | NULL

I'm performing the query in PHP and using:

while($row=mysql_fetch_assoc($results)) { ...blah.... }

But for harry $row['eid'] is blank. I'm not sure how to work this one out so any points would be greatly apprecited. Many thanks :)

Wiseguy · Accepted Answer · 2011-08-31 15:55:07Z

1

The eid field from tblEmployee isn't empty, but the eid field from tblMappings is empty, which is expected since there's no matching record in that table. Specify which fields you want instead of SELECT * ....

SELECT e.*, m.mid, m.cid FROM `tblEmployee` e
LEFT JOIN `tblMappings` m ON 
e.`eid` = m.`eid`

answered Aug 31, 2011 at 15:55

Wiseguy

20.9k9 gold badges68 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Baldrick Over a year ago

I used SELECT * because there are more employee fields like last name and address etc, and I do actually want them all, but this is new syntax you have provided me with, something to go and read about which has fixed my problem. Thanks very much :)

ajreal · Accepted Answer · 2011-08-31 15:53:44Z

1

SELECT tblEmployee.* FROM `tblEmployee`
LEFT JOIN `tblMappings` ON `tblEmployee`.`eid` = `tblMappings`.`eid`

Both tblEmployee and tblMappings also contains column eid,
and naturally the second eid from tblMappings is override the first one.

To solve this, you can specify the table name in the return columns

answered Aug 31, 2011 at 15:53

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

Comments

Konerak · Accepted Answer · 2011-08-31 15:55:51Z

1

You should not be using SELECT *. It's a bad habit.

Instead, consider this:

SELECT `tblEmployee`.`eid` FROM `tblEmployee`
LEFT JOIN `tblMappings` ON 
`tblEmployee`.`eid` = `tblMappings`.`eid`

answered Aug 31, 2011 at 15:55

Konerak

39.8k13 gold badges102 silver badges121 bronze badges

Collectives™ on Stack Overflow

mysql left join with nulls in php

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related