MySQL How to SELECT where field equals something in another table?-CodePudding

Take two example tables:

TableOne
 ---- ---------- ------------- ------------ ------------ -- 
| id | uid      | thingone    | thingtwo   | thingthree |  |
 ---- ---------- ------------- ------------ ------------ -- 
|  1 | 7        | 0           | 1          | 1          |  |
|  2 | 8        | 1           | 1          | 0          |  |
 ---- ---------- ------------- ------------ ------------ --

and

TableTwo
 ---- ---------- ------------- ------------ ------------ -- 
| id | oid      | thingone    | thingtwo   | thingthree |  |
 ---- ---------- ------------- ------------ ------------ -- 
|  1 | 7        | Apple       | Coconut    | Grape      |  |
|  2 | 8        | Potato      | Orange     | Banana     |  |
 ---- ---------- ------------- ------------ ------------ --

So in this example userid 7 would get Coconut and Grape, but not Apple. Userid 8 would get Potato and Orange, but not banana. I am wanting to do this the most efficient way and I realize I could do two separate queries with some php processing, but I want to do it with a single query if feasible.

What I need to do is (in a single query) get the row from TableOne by id and then select the row from TableTwo where theoid = the id of TableOne but only get the corresponding row from TableTwo if it is 1 and not 0 in TableOne.

Can any one help please?

CodePudding user response：

You can achieve it by using IF and comparing the columns from both tables 1 by 1. To compare the tabletwo from tableone you need to use JOIN, in this query I use a LEFT JOIN.

SELECT a.id, a.`uid`, 
IF(a.`thingone`=1, b.`thingone`, NULL) AS thingone,
IF(a.`thingtwo`=1, b.`thingtwo`, NULL) AS thingtwo,
IF(a.`thingthree`=1, b.`thingthree`, NULL) AS thingthree FROM tableone a
LEFT JOIN tabletwo b ON uid=oid;

Check MySQL IF() Function for more details.

CodePudding user response：

you need to make relational tables and then use join query

CodePudding user response：

What about this:

select case when t1.thingone = 1 then t2.thingone else '' end,
       case when t1.thingtwo = 1 then t2.thingtwo else '' end,
       case when t1.thingthree = 1 then t2.thingthree else '' end
from TableOne t1 join TableTwo t2 on t1.uid=t2.oid

You might need to concatenate these three or convert them into three rows, depending how how the three things should be represented.