Pulling from Two Tables with Different Number of Records

Posted on

Question :

I have three tables, one for members with 8 records, one for tasks with 1 record, and one for events with 3 records. The tasks table associates the events with members using one-to-many primary to foreign keys, creating a many-to-many relationship (so the columns are TaskID, MemberID, and EventID in the Tasks table).

I want to pull member data from the members table and the tasks data from the tasks table. Instead of showing the one Task record with the member name in the adjacent column, MySQL is listing ALL 8 members adjactent to the same Task, but only one of the members is actually associated with that task. This is what I have tried:

SELECT EventID, CONCAT(Title, " ", LastName) AS Member FROM Events, Members

How do I get MySQL to list only the tasks in the tasks table with the associated member CONCAT from the members table?

Answer :

Sounds like you just need to do an INNER JOIN. And it also sounds like you don’t know what one is? Recommend you read up: http://www.mysqltutorial.org/mysql-inner-join.aspx

In your case it would be a simple:

SELECT EventID, CONCAT(m.Title, " ", m.LastName) AS Member 
FROM Events e
INNER JOIN Tasks t ON e.EventID = t.EventID
INNER JOIN Members m ON t.MemberID = m.MemberID
WHERE EventID = <event ID>

Here, I am assuming you wish to get the information for a particular event? If not, remove the WHERE statement.

Leave a Reply

Your email address will not be published.