CODEWITHSUNDEEP
phpmysqlsql
Death Programmer
Death Programmer

Reputation: 896

multiple select result in MySQL depend on other select

I have database like this:

schema

For example, a user requests all posts data with posts.post_id = 1. Now I want to get additional data from other tables:

In PHP I can simply run 5 queries to get this information, but I guess this is not the best way because MySQL is able to perform this faster.

Update detail:

-- select from posts
SELECT
    posts.post_id,
    posts.post_title,
    posts.post_name,
    posts.post_content,
    posts.post_status,
    posts.post_type,
    posts.post_modified_gmt,
    posts.post_date_gmt,
    posts.post_mime_type,
    posts.user_id
FROM
    posts
WHERE
    posts.post_id = 1

--select from postmeta
SELECT
    postmeta.postmeta_value,
    postmeta.postmeta_key,
    postmeta.postmeta_id
FROM
    postmeta
WHERE
    postmeta.post_id = 1


SELECT
    files.file_size,
    files.file_type,
    files.file_name,
    files.file_id
FROM
    files
INNER JOIN file_relationships ON files.file_id = files.file_id
WHERE
    file_relationships.post_id = 1

--select from terms
SELECT
    terms.term_id,
    terms.term_name,
    terms.term_slug,
    terms.term_taxonomy,
    terms.term_description,
    terms.term_group,
    terms.term_parent,
    terms.term_count
FROM
    term_relationships
INNER JOIN terms ON term_relationships.term_id = terms.term_id
WHERE
    term_relationships.post_id = 1

--select from users , users.user_id depend on posts.user_id 
SELECT
    users.user_id,
    users.user_name,
    users.user_email,
    users.user_nicename,
    users.user_password,
    users.user_displayname,
    users.user_role,
    users.user_bio
FROM
    users
WHERE
    users.user_id = 1

this is query i run to get information with PHP, database + data

Is there a way to do this using MySQL only?

Upvotes: 3

Views: 1336

Answers (2)

FuzzyTree
FuzzyTree

Reputation: 32392

A single query isn't always better than multiple ones. In this case I recommend combining only your 1 to 1 relationships but keeping separate queries for your many to 1 relationships because if a single post has 3 terms and 2 files joining both many to 1 relationships would produce 6 rows.

Select post, user and postmeta data

select * from posts p
join postmeta pm on pm.post_id = p.post_id
join users u on u.user_id = p.user_id
where p.post_id = 1

Select files

select f.* from files f
join file_relationships fr on fr.file_id = f.file_id
join posts p on p.post_id = fr.post_id
where p.post_id = 1

Select terms

select t.* from terms t
join term_relationships tr on tr.term_id = t.term_id
join posts p on p.post_id = tr.post_id
where p.post_id = 1

Upvotes: 3

Brian DeMilia
Brian DeMilia

Reputation: 13248

This should be all the JOINs you need, modify the SELECT list as desired.

select p.post_id,
       p.post_title,
       p.user_id,
       u.user_name,
       u.user_email,
       pm.*,
       f.*
  from posts p
  join postmeta pm
    on p.post_id = pm.post_id
  join users u
    on p.user_id = u.user_id
  join file_relationships fr
    on p.post_id = fr.post_id
  join files f
    on fr.file_id = f.file_id

The * indicates 'all columns' from the table with the indicated alias.

Upvotes: 1

Related Questions