Mysql Join 4 tables and generate boolean field on the fourth one

This is what actually driving me crazy.

Three tables: candidats, offres, and candidatures.

There's fourth one called "cvtheque" having fields: recruteur_id and candidat_id and others..

I want to add a column on results that returns boolean variable

if(cvtheque.recruteur_id=candidatures.recruteur_id and    
cvtheque.candidat_id=candidatures.candidat_id) 

The folowing query returns results cols as:

    c_id    sexe    nom_prenom  cv_title    remplissage     offre_id    titre   
    candidature_id  lettre_id   entry_date

    SELECT 
    candidats.id as c_id, candidats.sexe, candidats.nom_prenom, 
    candidats.cv_title, candidats.remplissage, 

    offres.id AS offre_id, offres.titre, 
    candidatures.id as candidature_id, candidatures.lettre_id, candidatures.entry_date

    FROM candidats, offres JOIN candidatures
    WHERE candidatures.candidat_id = candidats.id
    AND candidatures.recruteur_id = '1'
    AND candidatures.offre_id = offres.id 
    ORDER BY candidatures.entry_date desc 
    LIMIT 0, 10

what i exactly i want is to add a column Boolean after the last column (entry_date) hol开发者_如何学编程ds a Boolean variable value of the condition (cvtheque.recruteur_id=candidatures.recruteur_id and cvtheque.candidat_id=candidatures.candidat_id)

I think you are looking for CASE WHEN, which is explained here.

Or perhaps a simple IF will do it -- documentation SELECT IF(1<2,'yes','no');

SELECT ..... candidatures.entry_date,
IF(cvtheque.recruteur_id=candidatures.recruteur_id 
AND cvtheque.candidat_id=candidatures.candidat_id,'yes','no')
FROM....

I am not certain of the syntax for IF condition1 AND condition2 in MySql.

Try it first without the AND, and when that is working add the AND.