MySQL SELECT with IF statement to set variable from another table

Question

I want to show all the results from Table1 based on some select condition being true, and have a variable be set to 0 or 1 for each result from Table1 based on some satisfied condition with Table2.

SELECT * FROM Table1 WHERE Some_Condition=true

Foreach Table1.Name
    SELECT IF(TID IS NULL, 0, 1) AS Variable FROM Table2 
    WHERE 
        Table2.Name=Table1.Name AND Table2.Val='p'

How can I make this all into one SQL call?

example call I would like to see is:

Table1:

+----+-------------------+
| ID |        Name       |
+----+-------------------+
| 1  |        John       |
+----+-------------------+
| 2  |        Alan       |
+----+-------------------+

Table2: So here Alan exists AND Val='p', not just existing

+-------+-----------+-----+
|  TID  |    Name   | Val |
+-------+-----------+-----+
|   1   |    Alan   |  p  |
+-------+-----------+-----+

SQL result I want from a SINGLE SELECT statement:

+------+----------+
| Name | Variable | 
+------+----------+
| John |     0    |
+------+----------+
| Alan |     1    |
+------+----------+

josemr · Accepted Answer · 2016-05-15 15:12:33Z

1

A LEFT JOIN and a CASE statement may work for you. Please see query below.

SELECT A.Name AS item, (CASE WHEN B.Val='p' THEN 1 ELSE 0 END) AS Variable 
FROM Table1 A LEFT JOIN Table2 B ON (A.Name=B.Name)

answered May 15, 2016 at 15:12

josemr

2632 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bob2u Over a year ago

I will try this and let you know results. I think this is what I was looking for. The actual query from A table is a bit more complicated, but I will try to merge this solution into it.

Bob2u Over a year ago

Thanks, this did what I wanted! I've not used CASE before, thanks for teaching me something new ;)

josemr Over a year ago

You're welcome! Please feel free to contact me if you have any questions.

Gordon Linoff · Accepted Answer · 2016-05-15 14:34:59Z

0

I think you just want a JOIN:

SELECT t2.Name, IF(Tt2.ID IS NULL, 0, 1) AS Variable
FROM Table2 t2 JOIN
     Table1 t1
     ON t2.Name = t1.Name
WHERE t2.Val = 'p' AND <some condition on t1> = true;

In MySQL, you can simplify the SELECT to:

SELECT t2.Name, (Tt2.ID IS NOT NULL) AS Variable

Note that I added the name to the SELECT, although it is not in your sample SQL.

answered May 15, 2016 at 14:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Andrew · Accepted Answer · 2016-05-15 15:13:51Z

0

You need LEFT JOIN Table2 to include all rows from Table1 even if row in Table2 not exists. And then in Variable column just check if Table2.TID is presented (i.e. not null).

SELECT Name, (Table2.TID IS NOT NULL) AS Variable
FROM Table1
  LEFT JOIN Table2 ON Table2.Name=Table1.Name AND Table2.Val='p'

Or it can be done with IF():

SELECT Name, IF(Table2.TID IS NULL, 0, 1) AS Variable
FROM Table1
  LEFT JOIN Table2 ON Table2.Name=Table1.Name AND Table2.Val='p'

answered May 15, 2016 at 15:13

Andrew

1,86615 silver badges15 bronze badges

Collectives™ on Stack Overflow

MySQL SELECT with IF statement to set variable from another table

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related