1

I am having trouble with a MySQL query (version 5.6.37). I think it merely needs a reorganizing of the query components, but I can not make it work.

Problem: when my JOIN returns no rows, the entire query returns no rows, even though the data matches the query.

Here is my current query (where '@' is the input):

SELECT pets.id,pet,collar,GROUP_CONCAT(petData.fleas) AS f_id
FROM titles 
    JOIN petWear    ON pets.id    = petWear 
    JOIN petData    ON petWear.id = petData.id 
WHERE pets.id = '@'
GROUP BY pets.id,pet,collar

Assuming a "pets" table like this:

id | pet
1  | cat
2  | dog
3  | fish
4  | snake
5  | rabbit

And a JOINed "petData" table like this:

id | fleas
1  | 1
1  | 2
1  | 3
1  | 4
2  | 5

Successful query: If @ = 1, then the query returns a single result:

id | pet | collar | f_id
1  | cat | gold   | 1,2,3,4

Unsuccessful query: If @ = 5, then the query returns no result.

What I would like to have returned (for @ = 5) is this single result (i.e. no result, or NULL, for "f_id"):

id | pet    | collar | f_id
5  | rabbit | red    |

Note that I have included the petWear table with the "collar" listing, just to state that a "normal" join needs to also be part of the picture.

Parapluie
  • 714
  • 1
  • 7
  • 22
  • 1
    Have you tried `LEFT OUTER JOIN petData`? – Bill Karwin Oct 09 '17 at 20:37
  • 1
    Also, thank you for the clear explanation of what you're trying to do! – Bill Karwin Oct 09 '17 at 20:38
  • Thank you, Bill and Jovi. I don't use SQL every day, and pointing me to the different JOIN *types* got me re-reading parts of the manual I haven't read in 15 years! Changing `JOIN petData ` to `LEFT JOIN petData` was all I needed. Thanks for your help. – Parapluie Oct 10 '17 at 10:19

1 Answers1

0

Try the following code. Basically, if GROUP_CONCAT returns no values, it will print no_fleas 

SELECT pets.id,pet,collar,IFNULL((GROUP_CONCAT(petData.fleas), 'no_fleas') AS f_id
FROM titles 
JOIN petWear    ON pets.id    = petWear 
JOIN petData    ON petWear.id = petData.id 
WHERE pets.id = '@'
GROUP BY pets.id,pet,collar
Sami
  • 83
  • 2
  • 10
  • Thanks, Sami. I was looking for something like this. Unfortunately, it did not work in MySQL v. 5.6.37. I used the above recommendation to employ LEFT JOIN. – Parapluie Oct 10 '17 at 10:15