MySql Join Boolean View Table

Question

I have a user table and a presentation table that lists some user IDs ... They look something like this:

Users

User_ID  |   Name       |   Age   | ...
   555      John Doe        35
   556      Jane Doe        24
   557      John Smith      18

View_Table

User_ID
  555
  557

Now when I run the query to retrieve the user:

SELECT User_ID, Name, Age FROM Users WHERE User_ID = 555

SELECT User_ID, Name, Age FROM Users WHERE User_ID = 556

I would also like to select a boolean, indicating if the user I'm retrieving is present in View_Table.

Result:

   User_ID           Name          Age      In_View
    555             John Doe       35         1
    556             Jane Doe       24         0

Any help would be greatly appreciated. Efficiency is a huge plus. Thank!!

Answer 1

SELECT Users.User_ID,Name,Age, View_Table.User_ID IS NOT NULL AS In_View
FROM Users 
LEFT JOIN View_table USING (User_ID)
WHERE User_ID = 555

Answer 2

SELECT 
   User_ID, Name, Age, 
   CASE WHEN v.UserID is not null THEN 1 ELSE 0 END AS In_View
FROM Users u
LEFT JOIN View_Table v on u.User_ID = v.UserID
WHERE UserID ...;

Answer 3

LEFT JOIN. , / User_ID, .

SELECT User_ID,Name,Age, IF(View_Table.User_ID, 1, 0) AS In_View
FROM Users LEFT JOIN View_Table USING(User_ID)
WHERE User_ID = 555

Answer 4

I know this is an "old" question, but it just happened, and none of these answers were so good. So I thought I would drop my 2 cents

SELECT
    u.User_ID,
    u.Name,
    u.Age,
    COALESCE((SELECT 1 FROM View_Table AS v WHERE v.User_ID = u.User_ID ), 0) AS In_View
FROM
    Users AS u
WHERE
    u.User_ID = 555

Just select 1with a correlated query (or null) to get 0we can use a convenient function COALESCEthat returns the first non-zero value from left to right.