CODEWITHSUNDEEP
phpmysqlsql
Jonny Sooter
Jonny Sooter

Reputation: 2417

Can these 3 queries be combined into a single MySQL query?

I have 3 tables (tags, tag, portfolio)

tags has 2 fields -> tags_id(ai), tags_name

tag has 3 fields -> tag_id(ai), tag_oid(portfolio_id), tag_tid(tags_id)

portfolio has a lot of fields -> portfolio_id, etc

I want to match the portfolio records that have been tagged with 'PD-02'. I've been able to do this in three separate queries and have been trying to combine them into a single query. The tags table holds the tag name/title and then the tag table table creates a relationship with the tag names to other tables - like portfolio.

$tags = query_select(
    "SELECT * 
    FROM tags
    WHERE tags_name = 'PD-02'");

if (!empty($tags)) {
    $related_portfolios = query_select(
        "SELECT *
        FROM tag
        WHERE tag_tid = $tags[0]['tags_id']"
    );

    if (!empty($related_portfolios)) {
        $portfolios = array();
        foreach ($related_portfolios as $tkey => $tag) {
            $portfolios[] = query_select("SELECT * FROM portfolio WHERE portfolio_id = $tag['tag_oid']");
        }
    }
}

Upvotes: 2

Views: 56

Answers (2)

RightClick
RightClick

Reputation: 1112

This query might work.

SELECT *
FROM tags, tag, portfolio
WHERE tags.tag_name='PD-02'
AND tags.tag_id=tags.tags_id
AND tags.tag_oid=portfolio.portfolio_id;

Upvotes: 0

potashin
potashin

Reputation: 44581

You can try with inner join:

select p.* 
from portfolio p
join tag t on p.portfolio_id = t.tag_oid
join tags ts on ts.tags_id = t.tag_tid
where ts.tags_name = 'PD-02'

Upvotes: 2

Related Questions