Home  >  Q&A  >  body text

php - Mysql's left join query, when a field in the right table is empty, why can't the corresponding field in the left table be found?

SELECT category.c_id,category.c_name,jobs.amount FROM qs_category as category 
LEFT JOIN qs_jobs as jobs ON category.c_id=jobs.trade 
WHERE jobs.addtime >= 1483200000 AND jobs.addtime <= 1498838400 

This is the query statement. The left table category is the classification table, and the right table jobs is the position table. I want to count the number of positions under each category.
But there is a problem with the result. If a certain category does not exist in the category field of the job table, that is to say, no one has added a position under this category, then this category will not be displayed in the query results.
But it stands to reason that if there is a left join, even if there is no corresponding data in the right table, the fields in the left table should still be displayed

高洛峰高洛峰2715 days ago435

reply all(2)I'll reply

  • 怪我咯

    怪我咯2017-06-05 11:10:00

    Of course, the table on the right where you wrote the where condition cannot be found. When the right table is empty, the condition field is also empty, and it is filtered out by your where condition.

    reply
    0
  • 漂亮男人

    漂亮男人2017-06-05 11:10:00

    I have also encountered this situation. It was filtered out by your where condition. The solution is to append the conditions written in where to on!
    where is to filter the result set after connecting the tables, but if the condition in on is false, the data in the left table will be returned, and the right table will be empty

    reply
    0
  • Cancelreply