CODEWITHSUNDEEP
mysqlsqljoin
user1876234
user1876234

Reputation: 857

Three tables join. Show query results even if one table is empty

These are two very similar queries. The difference between them is that in second query I add new table called ng2s3_map and I need only one column from there. The problem is that first query works all the time as needed but without outputting lik column, and the second query works only if there are rows in table ng2s3_map, otherwise it outputs nothing. How to change second query, so it would output results no matter if there's or there's no rows in ng2s3_map table. (The only reason I added 2 tables is to better explain my issue).

FIRST QUERY:

SELECT ng2s3_content.ID, ng2s3_similar.similar_ID, ng2s3_similar.up, ng2s3_similar.down, similar_articles.Title AS SimilarTitle, similar_articles.image AS image
FROM ng2s3_content
JOIN ng2s3_similar ON ng2s3_content.ID = ng2s3_similar.ID
JOIN ng2s3_content similar_articles ON ng2s3_similar.Similar_ID = similar_articles.ID
WHERE ng2s3_content.ID =4764

SECOND QUERY:

SELECT ng2s3_content.ID, ng2s3_similar.similar_ID, ng2s3_similar.up, ng2s3_similar.down, ng2s3_map.similar_id, ng2s3_map.vote_id, ng2s3_map.lik, similar_articles.Title AS SimilarTitle, similar_articles.image AS image
FROM ng2s3_content
JOIN ng2s3_similar ON ng2s3_content.ID = ng2s3_similar.ID
JOIN ng2s3_content similar_articles ON ng2s3_similar.Similar_ID = similar_articles.ID
JOIN ng2s3_map ON ng2s3_map.similar_id = ng2s3_similar.similar_ID
AND ng2s3_map.vote_id = ng2s3_content.ID
WHERE ng2s3_content.ID =4764

Upvotes: 0

Views: 516

Answers (1)

John Woo
John Woo

Reputation: 263733

If that's the case, then you need to use LEFT JOIN instead of INNER JOIN

SELECT  ng2s3_content.ID, 
        ng2s3_similar.similar_ID, 
        ng2s3_similar.up, 
        ng2s3_similar.down, 
        ng2s3_map.similar_id, 
        ng2s3_map.vote_id, 
        ng2s3_map.lik, 
        similar_articles.Title AS SimilarTitle, 
        similar_articles.image AS image
FROM    ng2s3_content
        INNER JOIN ng2s3_similar 
            ON ng2s3_content.ID = ng2s3_similar.ID
        INNER JOIN ng2s3_content similar_articles 
            ON ng2s3_similar.Similar_ID = similar_articles.ID
        LEFT JOIN ng2s3_map 
            ON  ng2s3_map.similar_id = ng2s3_similar.similar_ID AND 
                ng2s3_map.vote_id = ng2s3_content.ID
WHERE   ng2s3_content.ID = 4764

To further gain more knowledge about joins, kindly visit the link below:

Upvotes: 1

Related Questions