CODEWITHSUNDEEP
mysqlsql
BeBest
BeBest

Reputation: 319

select column as true / false if id is exists in another table

I have 2 tables, one for members and another one for their services. Those are InnoDB tables on MySQL 5.6 server.

Members table:

id     |       name       |   phone
----------------------------------------
  1       Daniel             123456789
  2       Liam               123456789
  3       Lucas              123456789

Services table:

 MID    |    profile    |     lastSeen
----------------------------------------
  1       2                  2014-08-13 14:23:23
  3       1                  2014-08-12 15:29:11

I try to achieve this result:

 id     | name      | services
---------------------------------
  1       Daniel      true
  2       Liam        false
  3       Lucas       true

So if the user ID is exists in services table, the column services will be true or false otherwise.

I tried to do it with JOINs and Sub-Queries without success, so I need your help ;)

Upvotes: 25

Views: 31527

Answers (3)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

You can use a simple query like this

SELECT
    m.id,
    m.name
    IF(s.MID,true,false) services
FROM members m
LEFT JOIN services s ON s.profile = m.id

Upvotes: 3

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

select m.id, m.name, case when s.mid is null then false else true end
from members m
left join services s
    on s.profile = m.id

Upvotes: 3

Girish
Girish

Reputation: 12127

use LEFT JOIN Services table, Try this query

SELECT members.id, members.name, 
       IF(services.mid IS NULL, FALSE, TRUE) as services
FROM members
LEFT JOIN services ON (members.id = services.mid)

Upvotes: 42

Related Questions