rexhin rexhin - 6 months ago 13
PHP Question

MySQL joins multiple tables

I have 5 tables:

comments(id, time, comment, sender, sender_type, user, milestone, time)


users(id, first_name)


representatives(id, first_name)


agents(id, first_name)


lawyers(id, first_name)


There are 5 different milestones where the users, representative, agents and lawyers will comment.
The receiver is always the user
and the sender may be either and agent, lawyer or a representative.

I want to get results from the comments table for a given user(id) and milestone.

so for ex. in milestone 2 I want to get all the comments to user 1 from representatives and agents. I want to display the comment, the name of the sender, time, the user who received the comment and the comment.

This is my query but I get no results (0 rows)

$milestones = array(
1 => ["representative"],
2 => ["representative", "agent"],
3 => ["representative"],
4 => ["representative"],
5 => ["representative", "lawyer"]
);

$allowed_types = '"' . implode('", "', $milestones[$milestone]) . '"';

$query = "
select c.time, c.comment, u.first_name as user, r.first_name as represantive, l.first_name as lawyer, a.first_name as agent
from comments c
join users u
on c.user = u.id
join representatives r
on c.sender = r.id
join lawyers l
on c.sender = l.id
join agents a
on c.sender = a.id
where
c.user = $user
and
c.sender_type in ($allowed_types)
group by c.time
order by time asc
";

Answer

Use left join instead:

select c.time, c.comment, u.first_name as user, r.first_name as represantive, l.first_name as lawyer, a.first_name as agent
from comments c
join users u
   on c.user = u.id
LEFT join representatives r
   on c.sender = r.id
LEFT join lawyers l
  on c.sender = l.id
LEFT join agents a
  on c.sender = a.id
where ...