MySQL merge column from different table in same row

Question

I am using nested Select statements to get a single row result from table 1, I need to append another column (COLX) from table 3 at the end of same row. I have tried UNION, but that results in 2 rows. any suggestions?

SELECT COL1,COL2,COL3 
FROM `table1` 
where COL1 IN ( 
  SELECT table2.COL1 FROM `table2` 
  where table2.START_HOUR <= HOUR(NOW()) 
  AND 
  table2.END_HOUR >  HOUR(NOW()) 
  AND 
  table2.COLZ IN (
    SELECT IFNULL((SELECT COLX from `table3` WHERE COLY = 12345),'B'))) 
    Limit 1

table2.COLZ and table3.COLX are IDs used to match entries. Either of these in final result would fulfill the requirement.

edit (to further explain my table structure)

table1
COL1|COL2|COL3

table2
COl1|START_HOUR|END_HOUR|COLZ

table3
COLX|COLY

COLX is ID of table 3 to match COLZ in table 2

COL1 in ID of table 2 to match COL1 in table 1

result i need is table1.COL1,table1.COL2,table1.COL3, table2.COl1,table2.COLZ,table3.COLX

where WHERE table3.COLY = 12345

JBO JBO · Accepted Answer · 2017-08-02T08:00:34

If i read you well, your table2.COLZ and table3.COLX are IDs to match with table 1;

So you should make a join statement. Something like that :

SELECT COL1,COL2,COL3 
FROM `table1` t1
JOIN (
 SELECT table2.COL1, table2.colz FROM `table2` 
  WHERE table2.START_HOUR <= HOUR(NOW()) 
  AND 
  table2.END_HOUR >  HOUR(NOW()))
) t2 on t1.col1 = t2.col1
JOIN ( SELECT IFNULL((SELECT COLX from `table3` WHERE COLY = 12345),'B')) t3 on t3.colx = t2.colz

I haven't try this code so their might be some errors.

MySQL merge column from different table in same row

1 Answers