Bob2u Bob2u - 6 months ago 10
PHP Question

MySQL SELECT with IF statement to set variable from another table

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 |
+------+----------+

Answer

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)
Comments