Baseline
To get all fields from a table in a query with one or several joins. We wish avoid to write all name of fields from one table if the list of fields is really long.
Probleme
The following queries select all fields in all tables using “where” or “inner join”.
SELECT *
FROM table1, table2
WHERE table1.id=table2.table2_id
{AND ...others conditions}
SELECT *
FROM table1
INNER JOIN table2 ON table1.id=table2.table2_id
{AND ...others conditions}
the following queries don’t work (tested on postgreSQL).
SELECT table1.*
FROM table1, table2
WHERE table1.id=table2.table2_id
{AND ...others conditions}
SELECT table1.*
FROM table1
INNER JOIN table2 ON table1.id=table2.table2_id
{AND ...others conditions}
Error
ERROR: invalid reference to an entry in the FROM clause for table "table1" SQL state: 42P01
Solution
Using alias is the solution.
SELECT t1.*
FROM table1 t1, table2 t2
WHERE t1.id=t2.table2_id
{AND ...others conditions}
SELECT t1.*
FROM table1 t1
INNER JOIN table2 AS t2 ON t1.id=t2.table2_id
{and ...others conditions}