Search code examples
mysqlsqlsubqueryconcatenationinner-join

Add column with TRUE/FALSE if id matches in another table

This is my current query :

select * from modeles JOIN marques ON modeles.marque_id = marques.marque_id

I would like to add a column to the returned data called "isActive" and it should contain TRUE or FALSE (or 1 / 0) depending on whether another table (modeles_scrape) contains a row where modeles.display_name = modeles_scrape.marque

So, expected result :

modele_name;modele_id;marque_name;marque_id;isActive
"Captur";1;"Renault";1;TRUE

I undertand this is a question that has been asked already but the solutions I have found join the modeles_scrape table, which is not what I'm trying to achieve.

Solution

  • You can use exists and a correlated subquery:

    select mo.*, ma.*,  -- better enumerate the columns you want here
    exists (select 1 from modeles_scrape ms where ms.marque = ma.display_name ) as isactive
from modeles  mo
inner join marques ma using(marque_id)