Geek asks and answers

Not all data is returned from mysql query

Suppose 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;

Why doesn't he return a bob entry that has "spork" in it? You can see this request in action at http://sqlfiddle.com/#!2/cbbda/5

It goes without saying that this is not my actual code, but it is quite similar and has the same problem that I can figure out where my problem is.

+1
source share
2 answers

Because the default JOINvalue is equal INNER, the query does not return results for which the table returns NULL. Using LEFT JOINreturns the string "bob", the included values NULL.

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

JOIN: 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;
+3

table_1, JOIN , .

, JOIN LEFT JOIN . , "", table_1 table_2, null, .

+2

More articles:


All Articles