并非所有数据都从mysql查询返回 [英] Not all data coming back from mysql query

问题描述

让我们说如下创建3个表...

Lets say that I create 3 tables as follows...

create table `users` (`username` varchar(20), `other_non_relevant_field` varchar(20) );
insert into `users` values
("Bob","blah blah blah"),
("Steve","blah blah blah"),
("Sue","blah blah blah"),
("Adam","blah blah blah");


create table `table_1` (`username` varchar(20), `field_abc` varchar(20), `field_def` varchar(20), `other_non_relevant_field` varchar(20) );
insert into `table_1` values
("Steve","blue","brown","blah blah blah"),
("Sue","yellow","brown","blah blah blah"),
("Sue","pink","brown","blah blah blah"),
("Adam","green","brown","blah blah blah");


create table `table_2` (`username` varchar(20), `field_ghi` varchar(20), `field_jkl` varchar(20), `other_non_relevant_field` varchar(20) );
insert into `table_2` values
("Bob","spoon","fork","blah blah blah"),
("Bob","knife","spork","blah blah blah"),
("Steve","spoon","knife","blah blah blah"),
("Sue","spoon","fork","blah blah blah"),
("Sue","knife","spork","blah blah blah"),
("Adam","fork","knife","blah blah blah");

然后运行此查询...

and then I run this query...

SELECT users.username,
    table_1.field_abc, table_1.field_def,
    table_2.field_ghi, table_2.field_jkl
FROM users
JOIN table_1 ON table_1.username = users.username
JOIN table_2 ON table_2.username = users.username
WHERE
    table_1.field_abc REGEXP "(spork|pink)" OR
    table_1.field_def REGEXP "(spork|pink)" OR
    table_2.field_ghi REGEXP "(spork|pink)" OR
    table_2.field_jkl REGEXP "(spork|pink)"
ORDER BY
(
    ( CASE WHEN table_1.field_abc LIKE "%spork%" THEN 1 ELSE 0 END ) +
    ( CASE WHEN table_1.field_abc LIKE "%pink%" THEN 1 ELSE 0 END ) +
    ( CASE WHEN table_1.field_def LIKE "%spork%" THEN 1 ELSE 0 END ) +
    ( CASE WHEN table_1.field_def LIKE "%pink%" THEN 1 ELSE 0 END ) +
    ( CASE WHEN table_2.field_ghi LIKE "%spork%" THEN 1 ELSE 0 END ) +
    ( CASE WHEN table_2.field_ghi LIKE "%pink%" THEN 1 ELSE 0 END ) +
    ( CASE WHEN table_2.field_jkl LIKE "%spork%" THEN 1 ELSE 0 END ) +
    ( CASE WHEN table_2.field_jkl LIKE "%pink%" THEN 1 ELSE 0 END )
)DESC;

为什么它不返回其中包含"spork"的Bob的条目? 您可以在 http://sqlfiddle.com/#!2/cbbda/5上看到正在运行的查询

Why does it not return Bob's entry that has "spork" in it? You can see this query in action at http://sqlfiddle.com/#!2/cbbda/5

不用说，这不是我的实际代码，但是足够相似，并且存在相同的问题，我可以找出问题所在.

It goes without saying that this is not my actual code but it is similar enough and has the same issue that I'll be able to figure out where my problem is.

推荐答案

由于默认JOIN为INNER，因此查询不会返回表返回NULL的结果.使用LEFT JOIN返回鲍勃"行，其中包括NULL值.

Since the default JOIN is INNER, the query is not returning results for which a table returns NULL. Using LEFT JOIN returns the "Bob" row, NULL values included.

http://sqlfiddle.com/#!2/cbbda/7/0

以下是各种JOIN类型的直观表示: http://www.codeproject.com/Articles/33052/Visual -SQL联接的表示形式

Here is a visual representation of various JOIN types: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

SELECT users.username,
  table_1.field_abc, table_1.field_def,
  table_2.field_ghi, table_2.field_jkl
FROM users
LEFT JOIN table_1 ON table_1.username = users.username
LEFT JOIN table_2 ON table_2.username = users.username
WHERE
  table_1.field_abc REGEXP "(spork|pink)" OR
  table_1.field_def REGEXP "(spork|pink)" OR
  table_2.field_ghi REGEXP "(spork|pink)" OR
  table_2.field_jkl REGEXP "(spork|pink)"
ORDER BY
(
  ( CASE WHEN table_1.field_abc LIKE "%spork%" THEN 1 ELSE 0 END ) +
  ( CASE WHEN table_1.field_abc LIKE "%pink%" THEN 1 ELSE 0 END ) +
  ( CASE WHEN table_1.field_def LIKE "%spork%" THEN 1 ELSE 0 END ) +
  ( CASE WHEN table_1.field_def LIKE "%pink%" THEN 1 ELSE 0 END ) +
  ( CASE WHEN table_2.field_ghi LIKE "%spork%" THEN 1 ELSE 0 END ) +
  ( CASE WHEN table_2.field_ghi LIKE "%pink%" THEN 1 ELSE 0 END ) +
  ( CASE WHEN table_2.field_jkl LIKE "%spork%" THEN 1 ELSE 0 END ) +
  ( CASE WHEN table_2.field_jkl LIKE "%pink%" THEN 1 ELSE 0 END )
)DESC;

这篇关于并非所有数据都从mysql查询返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！