jppower175 jppower175 - 2 months ago 6
MySQL Question

MySQL pulling content from 2 tables that is connected by a third table

I currently have 2 tables in a database I need to get information from, "content" and "type". These two tables are linked by a 3rd table name "typeMembers." This is the structure:

Table Content:
id content link date isPublished
1 content 1 link 1 3/13/91 1
2 content 2 link 2 3/18/91 1
3 content 3 link 3 3/22/91 1

Table type:
id name
1 Event
2 Page
3 Test

Table typeMember
id type_id content_id
1 1 1
2 2 1
3 3 1
4 1 2
5 1 3


Currently I have my query set up as:

//using PDO in PHP
q = $dbc->prepare(
"SELECT a.id, a.content,a.date,a.link, c.name
FROM content a
LEFT OUTER JOIN typeMember b
ON b.content_id = a.id
LEFT OUTER JOIN types c
ON b.type_id = c.id
WHERE a.isPublished = 1
ORDER BY a.date DESC"
);
$r = $q->execute();


When this is returned I am getting 1 row for each typeMember in the database instead of content. What am I structuring wrong?

Data I would like to be returned:

id content link date name
1 content 1 link 1 3/13/91 Event, Page, Test
2 content 2 link 2 3/18/91 Event
3 content 3 link 3 3/22/91 Event


How it is being returned

id content link date name
1 content 1 link 1 3/13/91 Event
1 content 1 link 1 3/13/91 Page
1 content 1 link 1 3/13/91 Test
2 content 2 link 2 3/18/91 Event
3 content 3 link 3 3/22/91 Event


Edit: filing out the data actually made me realize what is going on. There is a 1 to many relationship with content to type. Is there a way to get all the types in one query?

Answer

for get the name in the same row you can use group_Concat

SELECT  a.id, a.content, a.date, a.link, group_concat(c.name )
FROM content a 
LEFT  JOIN typeMember b ON b.content_id = a.id 
LEFT  JOIN types c ON b.type_id = c.id  
WHERE a.isPublished = 1 
Group by  a.id, a.content, a.date, a.link
ORDER BY a.date DESC