SQLite - LEFT JOIN

Question

I need to make a query like this:

SELECT table1.*, table2.column 
FROM table1 
LEFT JOIN table2 ON table1.column = table2.column

But it's not working. If I try the same query but replacing the first part -- >

SELECT table1.column, table2.column 
FROM table1 
LEFT JOIN table2 ON table1.column = table2.column

it works fine. I need to bring all the columns from table1. How can I make it without specifing all of them?

What is the error ? Don't you have the same column name in both tables ? — kmas, Oct 28 '13 at 12:59
Using `SELECT *` or `SELECT table.*` in production is a bad practice. Consider to specify all columns explicitly. Read more http://stackoverflow.com/questions/3639861/why-is-select-considered-harmful — peterm, Oct 28 '13 at 13:02
Yes, it's the same name. And there's no error, I get all table1 columns but not the one I need. This is the real query SELECT property.*, ubication.name FROM property LEFT JOIN ubication ON ubication.id = property.ubication — Agustín, Oct 28 '13 at 13:02
[Works for me](http://www.sqlfiddle.com/#!5/5dc3b/1/0). Don't keep your table structure a secret! — CL., Oct 28 '13 at 14:41
grat, thanks! I didnt show the structure cause it's really big and I was at work! haha, thank you! — Agustín, Oct 29 '13 at 12:41

score 16 · Accepted Answer · edited May 23 '17 at 12:02

16

If you have the same column name in table1 and table2, here is the solution for not specifying all the column name of table1 :

SELECT table1.*, table2.column as my_column_name_no_in_table1
FROM table1 
LEFT JOIN table2 ON table1.column = table2.column

If the column names of table1 and table2 are all different, you can use :

SELECT table1.*, table2.*
FROM table1 
LEFT JOIN table2 ON table1.column = table2.column

But as said in the peterm's comment, it is not a good practice in production. Now, do as you want! ;)

edited May 23 '17 at 12:02

Community

answered Oct 28 '13 at 13:01

kmas

I'm still getting the same columns as I did before! – Agustín Oct 28 '13 at 13:06
And you can give us also the table structure if you want more help. – kmas Oct 28 '13 at 13:09
SELECT property.*, ubication.name FROM property LEFT JOIN ubication ON property.ubication = ubication.id That's it, there's no error shown in that query, but I'm getting all property columns but not the ubication one, my english sucks sorry – Agustín Oct 28 '13 at 13:12
Well, that's normal you don't get all the ubication columns, you just select the `name` column of ubication. – kmas Oct 28 '13 at 13:14
yea but I don't get that, I don't get any ubication columns! I only get property columns, its weird that if I change the "SELECT property.*" for a "property.anyColumn", it works! – Agustín Oct 28 '13 at 13:16
Mmmmh, your problem is not clear. If you select a column, you'll have it in your result set. Tell us how you query your database. – kmas Oct 28 '13 at 13:18

1 Answers1