CODEWITHSUNDEEP
mysql
G-J
G-J

Reputation: 1068

Not all data coming back from mysql query

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;

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.

Upvotes: 1

Views: 87

Answers (2)

tato
tato

Reputation: 5549

Because Bob is not present in table_1 elements, and the JOIN discards rows that don't have matching rows in all joined tables.

In order to achieve what you want, change JOIN by LEFT JOIN in the two appearances. This way you will have all 'Users' elements joined with table_1 and table_2 elements that match or null if no match is found.

Upvotes: 2

showdev
showdev

Reputation: 29168

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

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;

Upvotes: 3

Related Questions