Query not returning rows in a table that don't have corresponding values in another [associative] table
I have
Table: ARTICLES
ID | CONTENT
---------------
1 | the quick
2 | brown fox
3 | jumps over
4 | the lazy
Table: WRITERS
ID | NAME
----------
1 | paul
2 | mike
3 | andy
Table: ARTICLES_TO_WRITERS
ARTICLE_ID | WRITER_ID
-----------------------
1 | 1
2 | 2
3 | 3
To summarize, article 4 has no writer.
So when I do a "search" for articles with the word "the":
SELECT a.id, a.content, w.name
FROM articles a, writers w, articles_to_writers atw
WHERE a.id=atw开发者_运维技巧.article_id AND w.id=atw.writer_id AND content LIKE '%the%'
article 4 does not show up in the result:
ID | CONTENT | NAME
-----------------------
1 | the quick | paul
How do I make article 4 still appear in the results even though it has no writers?
Try the following query:
SELECT a.id, a.content, w.name
FROM articles a LEFT JOIN articles_to_writers atw ON a.id = atw.article_id
LEFT JOIN writers w ON w.id=atw.writer_id
WHERE a.content LIKE '%the%'
Your original query used an older SQL syntax creating implicit INNER joins between all tables. INNER joins require a matching record in each table joined.
In the revised query, above, the tables are "attached" using an LEFT OUTER joins. This will allow records on the left side of the join to appear in the final result set even if there's no matching row in writer. The columns from writer will have NULL values for these "extra" rows.
SELECT A.ID, A.CONTENT, W.NAME
FROM
ARTICLES A
LEFT JOIN ARTICLES_TO_WRITERS ATW ON ATW.ARTICLE_ID = A.ID
LEFT JOIN WRITERS W ON W.ID = ATW.WRITER_ID
WHERE A.CONTENT LIKE '%the%'
In ANSI format:
SELECT a.id, a.content, w.name
FROM articles a
LEFT JOIN articles_to_writers atw on a.id=atw.article_id
LEFT JOIN writers w on w.id=atw.writer_id
WHERE
acontent LIKE '%the%'
This is a left join, which means that all the articles will be displayed, even if there is no writer.
精彩评论