lizcoder lizcoder - 4 months ago 7
MySQL Question

SELECT query in many-to-many relationship isn't showing all 'tags'

I've been working on this problem for a few hours now. I have two tables,

animals
and
characteristics
, that have a many-to-many relationship and form a third table,
animal_char
. In the table
animal_char
, AID is a foreign key to ID in the table
animals
, and CID is a foreign key to ID in the table
characteristics
.
characteristics
also has a description attribute, and that is what I want to search on. My goal is to be able to search for one, two, or three descriptions in
characteristics
, and have the output show rows for each animal ID that contains those descriptions. In addition, I want to be able to see all the other characteristic descriptions for each animal ID, even if I did not search for them.

I've tried countless queries, but here is an example of one that isn't working:

SELECT a.ID,
a.name,
GROUP_CONCAT(c.description ORDER BY c.description SEPARATOR ', ')
FROM animals a
INNER JOIN animal_char ac ON ac.AID = a.ID
INNER JOIN characteristics c ON c.ID = ac.CID
WHERE c.description = 'Lab'
GROUP BY ID;


Problems with this code:


  1. I can only search one characteristic description

  2. The results only show that Lab is a characteristic; they don't show any other characteristic for each row. I want to be able to see that some rows have multiple characteristics.



Thank you so much in advance!

EDIT
Well, I figured out the query for searching for one characteristic at a time. I might say that's good enough for the purposes of this project. The query is:

SELECT a.ID, a.name,
GROUP_CONCAT(c.description ORDER BY c.description SEPARATOR ', ')
FROM animals a
INNER JOIN animal_char ac ON a.ID = ac.AID
INNER JOIN characteristics c ON c.ID = ac.CID
WHERE a.ID IN
(SELECT ac.AID FROM characteristics c
INNER JOIN animal_char ac ON ac.CID = c.ID
WHERE c.description = 'Lab')
GROUP BY a.ID

Answer

Your problem is that your query includes only characteristics where description = 'Lab' that there may be other characteristics for these animals has been completely excluded by your Where clause.

What you need to do is separate the filter from the data you are looking for. One way of doing this would be to join the characteristics table twice. Once for the filter (fc) , and once for the results (c).

SELECT a.ID, 
   a.name, 
   GROUP_CONCAT(c.description ORDER BY c.description SEPARATOR ', ') 
FROM animals a 
    INNER JOIN animal_char ac ON ac.AID = a.ID
    INNER JOIN characteristics c ON c.ID = ac.CID 
    INNER JOIN characteristics fc on ac.CID = fc.ID
WHERE fc.description = 'Lab' 
GROUP BY a.ID;

As you found yourself with your edit, there are other ways to do this as well. I'm many years removed from working with mysql so I'll refrain from attempting to comment on the relative merits of the techniques.