Akash khan Akash khan - 23 days ago 8
MySQL Question

Conditionally add another column in query base on other query result

I found the solution for add query column by case when then from same table depending on column value but It can't solve my problem.

I have two tables.
Table: pools

id date title description
------ ---------- -------------- ---------------------
1 2016-11-10 This is pool 1 this is description


Table: votes

id date time pool_id option_id uid
------ ---------- -------- ------- --------- ------------------------------
1 2016-11-10 21:22:23 1 1 XodxfBfeRdZsOFRNbJ0AecMgpyo2
2 2016-11-10 21:22:23 1 2 PhbZ675XdZeL59QFKLQq8u1uQyg2


I want to query output by passing uid in query.

id date title voted
------ ---------- -------------- ---------------------
1 2016-11-10 This is pool 1 YES

Answer

You can LEFT JOIN 2 tables pools and votes. Then check if corresponding entry exists in votes, show YES else NO.

For better explanation, I created 1 more entry in pool table.

INSERT INTO pools VALUES (2, '2016-11-11', 'This is pool 2', 'this is description');

Below Query will give you desired output:

SELECT p.id, p.date, p.title, IF(v.id IS NOT NULL, 'YES', 'NO') AS voted
FROM pools p LEFT JOIN votes v
ON p.id = v.pool_id
AND v.uid = 'XodxfBfeRdZsOFRNbJ0AecMgpyo2';

Query Explanation

LEFT JOIN will return row from LHS table and NULL from RHS table if corresponding value is not present.

We can then add a check in SELECT statement to create custom column voted.

OUTPUT

id  date        title          voted
-------------------------------------
1  2016-11-10  This is pool 1  YES   
2  2016-11-11  This is pool 2  NO    
Comments