Get the matching results between two joining tables

Question

I have an Enterprise model :

name - VARCHAR 255

A Candidat model :

first_name - VARCHAR 255
last_name - VARCHAR 255

And a Type model :

name - VARCHAR 255

Each enterprise can select one or more types that they are interested in. Each candidat can do that as well.

I'm trying to display a list of all the candidats that share some types with the currently logged in enterprise. They don't need to have the exact same types, just share some of them.

I'm using two joining tables to keep the links between a candidat and a type, and an enterprise and a type.

id - INT
candidat_id - INT
type_id - INT

id - INT
enterprise_id INT
type_id INT

Here's the SQL query I'm using at the moment :

SELECT candidats.* FROM candidats
    INNER JOIN candidats_types ON candidats_types.candidat_id = candidats.id
    RIGHT JOIN entreprises_types ON entreprises_types.type_id = candidats.type_id
    WHERE entreprises_types.entreprise_id = 1

The 1 at the end of the query is the ID of the currently logged in enterprise, hard-coded for testing purposes.

However, this does not work, it returns an empty array, even though there are some candidates that share the same types as this enterprise.

What am I doing wrong with this query?

score 0 · Answer 1 · answered Dec 07 '15 at 19:39

According to the description of your tables, your query should generate an error, because candidate.type_id doesn't exist. I would suggest:

SELECT c.*
FROM candidats c INNER JOIN
     candidats_types ct
     ON ct.candidat_id = c.id INNER JOIN
     entreprises_types et
     ON et.type_id = ct.type_id
WHERE et.entreprise_id = 1;

From the description of your problem, RIGHT JOIN is not necessary.

Get the matching results between two joining tables

1 Answers1