SQL Select 1 column from each 2 or more different tables

Question

I have this tables in mysql:

students    time    class_student
someone     1   something01
someone     2   something02
someone     3   something03
someone     4   something04
someone     5   something05


theachers   time    class_teachers
someone     1   something11
someone     3   something12
someone     5   something13
someone     7   something14
someone     9   something15
someone     11  something16

and I need to get the class filds form each table order by all time in both tables, like this

    time    class_student class_teachers
    1   something01         something11
    2   something02
    3   something03         something12
    4   something04
    5   something05         something13
    7                       something14
    9                       something15
    11                      something16

first I test this query that the result is al times in both tables

SELECT time FROM table1
UNION
SELECT time FROM table2
ORDER BY time

I have this query that return the data but in the same fild

SELECT class_student FROM table1 WHERE time IN (
                                                    SELECT time FROM table2
                                                    UNION
                                                    SELECT time FROM table1
                                                    ORDER BY time
                                                    )
UNION
SELECT class_teachers FROM table2 WHERE time IN (
                                            SELECT time FROM table2
                                            UNION
                                            SELECT time FROM table1
                                            ORDER BY time
                                            );

and I try this one, but the sql doesn't execute it

SELECT
    class_student,
    class_teachers
FROM 
    table1
    inner join
    table2 on time IN (
                SELECT time FROM table1
                UNION
                SELECT time FROM table2
                ORDER BY time
                );

With this, I would use cross apply to fetch all of the students found in one class; a parent - child table relationship is done using this kind of join. — WickedFan, Dec 03 '14 at 23:15
If supported: `SELECT time,class_student,class_teachers FROM table1 a FULL JOIN table2 b ON a.time = b.time` If not: http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql — Hart CO, Dec 03 '14 at 23:24

score 1 · Answer 1 · answered Dec 04 '14 at 11:14

1

Try this.

SELECT CASE WHEN c.time IS NULL THEN a.time  ELSE c.time END AS time, c.class_student, a.class_teachers
FROM table1 c
FULL JOIN table2 a ON c.time = a.time

answered Dec 04 '14 at 11:14

Donovan

11
1

You don't need the case, `COALESCE(c.time,a.time) as time` is better – Hogan Dec 05 '14 at 15:25

Simon Puente · Accepted Answer · 2014-12-05T15:38:23.557

I found this solution

SELECT 
CASE 
WHEN table1.time IS NOT NULL THEN table1.time
WHEN table2.time IS NOT NULL THEN table2.time
WHEN table3.time IS NOT NULL THEN table3.time
END AS time,
table1.valor as table1, table2.valor as table2 , table3.valor as table3
FROM table1
LEFT OUTER JOIN table2 USING (time)
LEFT OUTER JOIN table3 USING (time)
UNION
SELECT 
CASE 
WHEN table1.time IS NOT NULL THEN table1.time
WHEN table2.time IS NOT NULL THEN table2.time
WHEN table3.time IS NOT NULL THEN table3.time
END AS time,
table1.valor as table1, table2.valor as table2 , table3.valor as table3
FROM table2
LEFT OUTER JOIN table1 USING (time)
LEFT OUTER JOIN table3 USING (time)
UNION
SELECT 
CASE 
WHEN table1.time IS NOT NULL THEN table1.time
WHEN table2.time IS NOT NULL THEN table2.time
WHEN table3.time IS NOT NULL THEN table3.time
END AS time,
table1.valor as table1, table2.valor as table2 , table3.valor as table3
FROM table3
LEFT OUTER JOIN table1 USING (time)
LEFT OUTER JOIN table2 USING (time)
ORDER BY time;

SQL Select 1 column from each 2 or more different tables

2 Answers2