SQL JOIN and ORDER BY

January 25, 2023

What is wrong with my query ?

SELECT 
    * 
FROM 
    table1
JOIN 
    table2 
ON 
    table2.table1_id = table1.id
WHERE 
    table_2.table3_id = 1 
ORDER BY 
    table2.id 
ASC

It does what I want except it doesn’t consider my ORDER BY and apparently keeps ordering by table1.id ASC by default instead of table2.id ASC.

It’s like my ORDER BY just doesn’t exist.

Note that there is a space between the closing quote and the ORDER BY (otherwise I would get an error message anyway, which I don’t)

Thanks.

>Solution :

The issue with your query is that in the ORDER BY clause, you are specifying "table2.id" but the query result doesn’t have a column named "table2.id", it only has "table1.id" and "table2.id".

You can change your query to:

$sql1 = "SELECT 
            table1.*, 
            table2.* 
         FROM 
            table1
         JOIN 
            table2 
         ON 
            table2.table1_id = table1.id
         WHERE 
            table_2.table3_id =".$_GET['table3_id']." 
         ORDER BY 
            table2.id 
         ASC";

This way you are selecting both the columns from table1 and table2 and it will order the result set by table2.id

Alternatively, you can also use an alias for table2 and use that alias in the ORDER BY clause like:

$sql1 = "SELECT 
            table1.*, 
            table2.* 
         FROM 
            table1
         JOIN 
            table2 
         ON 
            table2.table1_id = table1.id
         WHERE 
            table_2.table3_id =".$_GET['table3_id']." 
         ORDER BY 
            table2.id 
         ASC";

This way you can use the alias in the ORDER BY clause