CODEWITHSUNDEEP
mysql
lizcoder
lizcoder

Reputation: 43

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

Upvotes: 1

Views: 47

Answers (2)

Jakumi
Jakumi

Reputation: 8374

I see some useful ways to do the conjunction (animals that have the combined characteristics):

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' 
          OR c.description = 'second' 
          OR c.description = 'third'
       GROUP BY ac.AID
       HAVING COUNT(c.description) = 3)
GROUP BY a.ID

which in your subquery will filter by the desired descriptions and then group by animal id and filter again only those that have the desired number of descriptions. (the descriptions must be different, though) This also allows for some "2 out of 3 characteristics" searches. If you'd ever need them...

Second approach would be multiple wheres:

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')
  AND a.ID IN
      (SELECT ac.AID FROM characteristics c
       INNER JOIN animal_char ac ON ac.CID = c.ID
       WHERE c.description = 'second')
  AND a.ID IN
      (SELECT ac.AID FROM characteristics c
       INNER JOIN animal_char ac ON ac.CID = c.ID
       WHERE c.description = 'third')
GROUP BY a.ID

or as Mr. Mindor suggests (I find this mildly more approachable, putting it in the where clause works too of course):

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 fc1 ON ac.CID = fc1.ID AND fc1.description = 'Lab'
    INNER JOIN characteristics fc2 ON ac.CID = fc2.ID AND fc2.description = 'second'
    INNER JOIN characteristics fc3 On ac.CID = fc3.ID AND fc3.description = 'third'
GROUP BY a.ID;

I'm not certain which one of those three is actually the most performant. So you might try ... or not. ^^

Upvotes: 1

Mr.Mindor
Mr.Mindor

Reputation: 4129

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.

Upvotes: 2

Related Questions