CODEWITHSUNDEEP
phpmysqlselectjoin
Fibericon
Fibericon

Reputation: 5793

Selecting from multiple subtables

I have a table called meeting, and one called meeting_permissions. The latter stores what users took part in any given meeting. I also have other tables: note, discussion, conclusion, and topic, that store specific items for that meeting's transcript. What I want to do, is get all of the meetings that a specific user took part in, along with all of the associated notes, discussions, conclusions, and topics. Here's what I came up with:

SELECT * FROM meeting WHERE id IN
(SELECT meeting_id FROM meeting_permissions WHERE user_id = $user) 
LEFT JOIN conclusion ON meeting.id = conclusion.meeting_id 
LEFT JOIN discussion ON meeting.id = discussion.meeting_id 
LEFT JOIN note ON meeting.id = note.meeting_id 
LEFT JOIN topic ON meeting.id = topic.meeting_id

That doesn't work for me. It breaks on the second left join. What can I do to fix this?

Upvotes: 0

Views: 106

Answers (1)

Parallelis
Parallelis

Reputation: 719

You JOIN against your subselection instead your main meeting table:

SELECT 
  meeting.*,
  conclusion.*
  discussion.*,
  note.*,
  topic.*
FROM meeting 
  LEFT JOIN conclusion ON meeting.id = conclusion.meeting_id 
  LEFT JOIN discussion ON meeting.id = discussion.meeting_id 
  LEFT JOIN note ON meeting.id = note.meeting_id 
  LEFT JOIN topic ON meeting.id = topic.meeting_id
WHERE meeting.id IN
  (SELECT mp.meeting_id FROM meeting_permissions mp WHERE mp.user_id = $user)

Upvotes: 1

Related Questions