CODEWITHSUNDEEP
mysqlsql
Cat Named Dog
Cat Named Dog

Reputation: 1698

Select all data corresponding to row in table (SQL)

Edit: Using MySQL.

In this example I have a Posts table, a Users table, a Tagged_Posts table, and a Tags table.

The Posts table has a foreign key for user_id that corresponded to the Users table and id column.

The Tagged_posts table has two foreign keys, one for the tag_id and one for the post_id

The Tags table just has an id and a slug.

I'm trying to query posts and have all data related to that post returned, i.e The Post data, the user's data whom the post, and what tags belong to that post.

A single post can have many tags.

This is the SQL query I'm using:

$sql = "SELECT posts.title, users.name, users.handle, users.email, tags.slug as tags
from posts 
INNER JOIN users ON posts.user_id = users.id
INNER JOIN tagged_posts ON posts.id = tagged_posts.post_id
INNER JOIN tags ON tagged_posts.tag_id = tags.id";

Since this particular post has 3 tags associated with it, the query returns the same post 3 times with different values listed for tag. See below:

Array
(
    [0] => Array
        (
            [title] => This is my first Post!
            [name] => exampleuser
            [handle] => example
            [email] => [email protected]
            [tags] => database-design
        )

    [1] => Array
        (
            [title] => This is my first Post!
            [name] => exampleuser
            [handle] => example
            [email] => [email protected]
            [tags] => sql
        )

    [2] => Array
        (
            [title] => This is my first Post!
            [name] => exampleuser
            [handle] => example
            [email] => [email protected]
            [tags] => php
        )

)

Am I able to do a single query and get everything related to the post?

Upvotes: 1

Views: 220

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You want to concatenate the results together. For instance, in MySQL, you would do:

select p.title, u.name, u.handle, u.email, group_concat(t.slug) as tags
from posts p join
     users u 
     on p.user_id = u.id join
     tagged_posts tp
     on p.id = tp.post_id join
     tags t
     on tp.tag_id = t.id
group by p.title, u.name, u.handle, u.email;

Upvotes: 2

Related Questions