Joining 3 Tables In MySQL

Question

I am trying to join three tables together without any success so far. I managed to join two tables together and I am extracting the right data out but as soon as i add in a few more words to my sql statement, no data is being displayed. the code is as follows:

    <?php
$id = $_GET['meeting_id'];

$result = mysql_query("SELECT * FROM Meetings INNER JOIN Minutes ON Meetings.meeting_id = Minutes.meeting_id INNER JOIN attendees ON attendees.meeting_id AND attendees.username WHERE Minutes.approval = 'approved' AND Meetings.meeting_id = '$id'")
or die(mysql_error());

if (mysql_num_rows($result) == 0) {
       echo '<h3>There Arent Any Minutes For This Meeting Yet</h3>';
    } else {

while($info = mysql_fetch_array($result))
{

        echo "<tr>";
        echo "<td><br/>" .'Title: '. $info['title']." </td>";
        echo "<td><br/><br/>" .'Subject: '. $info['subject']. "</td>";
        echo "<td><br/><br/>" .'Next Subject: '. $info['next_subject']."</td>";
        echo "<td><br/><br/>" .'Attendees: '. $info['username']."</td>";


        }
    }
echo "</tr>";
echo "</table>";


?>

my database tables are as follows:

Meetings: meeting_id, title, subject, chairman, secretary, action

Minutes: minute_id, subject, next_subject, approval, meeting_id

attendees: attendees_id, meeting_id, username

anyone....?

What criteria are you joining with Attendees? It just says "meeting_id" and "user_name" on the attendees side. — Daryl Teo, Jan 06 '12 at 00:30
Why, I just wrote a really lengthy question and answer that covers this exact [sort of question](http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables) which I hope will help to clarify how joins work on tables and how to get information from multiple tables in your database! — Fluffeh, Sep 18 '12 at 14:08

score 3 · Accepted Answer · answered Jan 06 '12 at 00:32

3

The attendees table isn't being joined correctly.

SELECT * 
  FROM Meetings 
 INNER JOIN Minutes ON Meetings.meeting_id = Minutes.meeting_id 
 INNER JOIN attendees ON Meetings.meeting_id = attendees.meeting_id 
 WHERE Minutes.approval = 'approved' 
   AND Meetings.meeting_id = '$id'

Your SQL will only return meetings that have attendees and minutes. If a meeting doesn't have these then change the INNER JOIN statements to LEFT JOIN.

answered Jan 06 '12 at 00:32

Dave Barker

6,303
2
24
25

could you tell me why i am getting the same two data being shown on screen one underneath the other? – user1114080 Jan 06 '12 at 01:43

score 2 · Answer 2 · answered Jan 06 '12 at 00:32

2

SELECT Meetings.Meeting_Id,Meetings.Title,Meetings.Subject,
Minutes.Subject AS MinuteSubject,
Attendees.AttendeesID
FROM
Meetings
INNER JOIN
Minutes
ON Meetings.Meeting_id=Minutes.Meeting_Id
INNER JOIN
Attendees
ON Attendees.Meeting_id=Meetings.Meeting_ID
WHERE Meetings.Meeting_id=24

You can replace 25 with your variable

answered Jan 06 '12 at 00:32

Shyju

214,206
104
411
497

could you tell me why i am getting the same two data being shown on screen one underneath the other? – user1114080 Jan 06 '12 at 01:43
If you have more than one attendees for a single meeting ,the join will return you 2 records.But the AttendeeId will be different.This query is supposed to get all attendees belongs to a purticular meeeting. What result you are expecting ? – Shyju Jan 06 '12 at 02:06

Joining 3 Tables In MySQL

2 Answers2