user2065501 user2065501 - 3 months ago 12
MySQL Question

Select same row twice, and make the copy immutable

Due to me performing a left join that sometimes does not deliver a result, the ID of my "first" table somehow gets set to 0. I now want to select the ID twice, and make the copy immutable, so that I can still access it even when the join fails.

Here is my query:

SELECT * FROM table1
LEFT JOIN table2 ON table1.ID = table2.ID
LEFT JOIN table3 ON table1.ID = table3.ID


Each row in table1 only has the same ID in either table2 or table3, so, for each row, one of the two joins will "fail". Here's what I get as result (Mock result):

+------+------------+------------+------------+------------+------------+------------+
| ID | Table1Col1 | Table1Col2 | Table2Col1 | Table2Col2 | Table3Col1 | Table3Col2 |
+------+------------+------------+------------+------------+------------+------------+
| NULL | Something | Something | NULL | NULL | Something | Something |
| NULL | SomeValue | SomeValue | Something | Something | NULL | NULL |
+------+------------+------------+------------+------------+------------+------------+


What I want to achieve: Keep "ID" from being NULL.

(Also, selecting "table1.ID as MyID, *" gives me a SQL Error)

(Selecting values explicitely is out of question for design reasons)

Answer

The following query should work, naming the fields explicitly ...

SELECT table1.ID, table1.Table1Col1, table1.Table1Col2, 
       table2.Table2Col1, table2.Table2Col2, 
       table3.Table3Col1, Table3.Table3Col2
FROM table1
LEFT JOIN table2 ON (table1.ID=table2.ID)
LEFT JOIN table3 ON (table1.ID=table3.ID)

update for wildcards: You have to provide a table when * is not the only select "column".

SELECT table1.ID as MyID, table1.*, table2.*, table3.*
FROM table1
LEFT JOIN table2 ON (table1.ID=table2.ID)
LEFT JOIN table3 ON (table1.ID=table3.ID)

beware, that identical columns will appear multiple times this way so whatever processes the result should probably expect that. In PHP for example I believe the last value wins (if accessed by columnname).