I have three tables. Each one has three columns. It looks like this:
table 1 table 2 table 3 -------------------- -------------------- -------------------- col1 col2 colA col1 col2 colB col1 col2 colC 1 A data 3 C data 5 E data 2 B data 2 B data 6 F data 3 C data 1 A data 3 C data 4 D data 4 D data 2 B data 5 E data 6 F data 1 A data 6 F data 5 E data 3 C data
My question is if it is at all possible with JOINS to output something like this:
output table ----------------------------------------- col1 col2 colA colB colC 1 A data data data 2 B data data data 3 C data data data 4 D data data data 5 E data data data 6 F data data data
Note that Col1 and Col2 always have the same values in the different columns but are in different order.
I don't even know if this is possible but ideally the query would join the three tables and relate the information on col1 and col2 with each respective table and reorder the two joining tables and output a single table/array.
Let me know if this is really stupid or complex... I've been trying to wrap my head around this but by mySql knowledge is very limited.
SELECT *
FROM t1
LEFT JOIN t2 USING (col1, col2)
LEFT JOIN t3 USING (col1, col2)
Of course it's possible. As long as you have the same data across the tables, you can link them and display the data together.
For instance:
SELECT table1.*, colB, colC FROM table1, table2, table3
WHERE table1.col1 = table2.col1 AND table2.col1 = table3.col1
ORDER BY table1.col1 ASC;
would output the table in your question. I didn't use col2 as a condition in the example because it wasn't needed to link the data together.
Here is SQL for a simple double-join. Tweakable if you need to clarify anything I misunderstood.
SELECT
table1.col1,
table1.col2,
colA,
colB,
colC
FROM table1
JOIN table2
ON table1.col1 = table1.col1
AND table1.col2 = table2.col2
JOIN table3
ON table1.col1 = table3.col1
AND table1.col2 = table3.col2