CODEWITHSUNDEEP
phpmysql
Adam R
Adam R

Reputation: 69

Combining three queries into one

I have three pretty simple queries that I believe could be accomplished in one query, but I can't figure it out. Here are the queries:

$idSQL = "SELECT website_id FROM websites WHERE website_url = :webURL LIMIT 1

$featureSQL = "SELECT feature_id FROM feature_website WHERE website_id = :webID";

$sql = "SELECT feature_name, feature_start, feature_end, feature_headline, feature_text,  feature_photoHor, feature_photoVert, feature_photoSquare FROM features WHERE feature_id = :featID";

This gives me the expected output:enter image description here

So, I tried to combine the queries (probably using the incorrect Joins) into this:

$sql = "SELECT f.feature_name, f.feature_start, f.feature_end, f.feature_headline, f.feature_text,  f.feature_photoHor, f.feature_photoVert, f.feature_photoSquare FROM features LEFT JOIN feature_website fw ON f.feature_id = fw.feature_id LEFT JOIN websites w ON fw.website_id = w.website_id AND w.website_url = :webURL";

What am I missing?

Upvotes: 0

Views: 67

Answers (1)

Sebas
Sebas

Reputation: 21522

Try this query:

$sql = "
SELECT f.feature_name, f.feature_start, f.feature_end, f.feature_headline, f.feature_text,  f.feature_photoHor, f.feature_photoVert, f.feature_photoSquare 
FROM 
    websites w
        LEFT JOIN feature_website fw ON fw.website_id = w.website_id
        LEFT JOIN features f ON f.feature_id = fw.feature_id
WHERE
    w.website_url = :webURL
";

Maybe you could use regular JOIN in case you don't want to show any result if there's no feature for your website.

Upvotes: 2

Related Questions