CODEWITHSUNDEEP
phpmysql
Nico
Nico

Reputation: 13

MySQL Select: find records with related records of only one type

I'm pretty new to MySQL (coming from FileMaker) and struggle with this. I have to create a list from a MySQL database that meets this definition: List all records from table Children that do not have any related record in table Consultations where the content of field Category is different from first contact.

So the list should include children without any consultation as well as those who have only first contact consultation but nothing else.

My solution seems to work but is very slow even with a small set of sample records. Is there a simpler and/or faster way to do this?

I tried

SELECT
   Chi.ID, Chi.Name
FROM
   Children Chi JOIN Consultations Con ON Con.fk_Child = Chi.ID
WHERE
   NOT EXISTS (SELECT * FROM Consultations WHERE fk_Child = Chi.ID AND Categorie != 'first contact') 
GROUP BY
   Chi.ID

Upvotes: 0

Views: 1236

Answers (1)

vakata
vakata

Reputation: 3886

You do not need to JOIN the consultations table (and consequently - no need to group the results), you can also omit the * in the subquery:

SELECT Chi.ID, Chi.Name FROM Children Chi 
WHERE NOT EXISTS 
    (SELECT 1 FROM Consultations WHERE fk_Child = Chi.ID AND Categorie != 'first contact')

The EXISTS statements are one of the fastest for this kind of operation, if removing the JOIN and the GROUP is not yet fast enough, you will have to look at indexes and data types in those tables - for this particular query - make sure that Categorie is an ENUM field if possible, and add an index on fk_child.

Upvotes: 1

Related Questions