MySQL LEFT JOIN-值可以为NULL [英] MySQL LEFT JOIN - Values can be NULL

问题描述

我正在修改MySQL语句，但是LEFT JOIN和空白行有一些麻烦.目前，我有:

SELECT cs.case_id, cs.child_firstname, gu.*
FROM tblcases AS cs 
LEFT JOIN tblguardians AS gu
ON cs.case_id = gu.case_id 
WHERE cs.child_firstname = '%craig%' AND gu.firstname LIKE '%sally%'

只要"tblguardians"表中有一个条目，此查询就可以正常工作，但是如果没有记录，则LEFT JOIN返回NULL-因此，当我遍历PHP中的结果时，不会返回该条目. /p>

是否有一种方法可以从"tblcases"返回字段，而无论"tblguardians"是否有关联的结果?我想我已经读了太多的Google页面，以获取更多信息！

解决方案

在WHERE子句中的联接表上放置条件将有效地使联接成为INNER JOIN.
为避免这样做，您必须在LEFT JOIN中添加第二个条件:

 SELECT cs.case_id, cs.child_firstname, gu.*
   FROM tblcases cs 

        LEFT JOIN tblguardians gu
        ON cs.case_id = gu.case_id 
    AND gu.firstname LIKE '%sally%' /* <- this line was moved up from WHERE */

  WHERE cs.child_firstname = '%craig%' 

I'm modifying a MySQL statement but having a few troubles with LEFT JOIN and blank rows. At the moment I have:

SELECT cs.case_id, cs.child_firstname, gu.*
FROM tblcases AS cs 
LEFT JOIN tblguardians AS gu
ON cs.case_id = gu.case_id 
WHERE cs.child_firstname = '%craig%' AND gu.firstname LIKE '%sally%'

This query works fine as long as there is an entry in the "tblguardians" table, but the LEFT JOIN returns NULL if there is no record - therefore the entry isn't returned when I iterate over the results in PHP.

Is there a way to return the fields from "tblcases" whether or not "tblguardians" has an associated result? I think I've read too many pages of Google for much more information to sink in!

解决方案

Putting a condition on a joined table in your WHERE clause effectively makes that join an INNER JOIN.
To avoid doing that you have to add the second condition to your LEFT JOIN:

 SELECT cs.case_id, cs.child_firstname, gu.*
   FROM tblcases cs 

        LEFT JOIN tblguardians gu
        ON cs.case_id = gu.case_id 
    AND gu.firstname LIKE '%sally%' /* <- this line was moved up from WHERE */

  WHERE cs.child_firstname = '%craig%' 

这篇关于MySQL LEFT JOIN-值可以为NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！