2. PHPRunner General questions
  3. Joined query giving a weird error message
This topic is locked

Joined query giving a weird error message

 		2/27/2025 11:55:39 AM
PHPRunner General questions
W
 wijninga author

Hi,

I have a table that holds an ID field and an Email field and there is another table that hold the streetname based on the ID field. As I want to show per street how many people with and how many people without an email address we have, I created a double, joined query as per below:

select

  • FROM
    (SELECT
    COUNT(Bewoners.id) AS MetEmailadres,
    CRIB.streetname_nl AS CRIBstreetname_nl
    FROM
    Bewoners
    LEFT OUTER JOIN CRIB ON CRIB.address_id = Bewoners.address_id
    WHERE
    LENGTH(Bewoners.Email) >3
    GROUP BY
    CRIB.streetname_nl
    ) AS A
    INNER JOIN (SELECT
    COUNT(Bewoners.id) AS ZonderEmailadres,
    CRIB.streetname_nl
    FROM
    Bewoners
    LEFT OUTER JOIN CRIB ON CRIB.address_id = Bewoners.address_id
    WHERE
    LENGTH(Bewoners.Email) <3
    GROUP BY
    CRIB.streetname_nl
    ) AS B ON B.streetname_nl = A.CRIBstreetname_nl

In the query editor in PHP this works perfectly and show exactly what I want. But when I build and click on the view, I get a weird error:

Technische informatie
Fouttype 256
Fout beschrijving Unknown column '15' in 'order clause'
URL localhost/Bewoners3_list.php?
Fout bestand C:\runnerapps\Bewonerslijst\connections\Connection.php
Fout regel 669
SQL query select * FROM (SELECT COUNT(Bewoners.id) AS MetEmailadres, CRIB.streetname_nl AS CRIBstreetname_nl FROM Bewoners LEFT OUTER JOIN CRIB ON CRIB.address_id = Bewoners.address_id WHERE LENGTH(Bewoners.Email) >3 GROUP BY CRIB.streetname_nl ) AS A INNER JOIN (SELECT COUNT(Bewoners.id) AS ZonderEmailadres, CRIB.streetname_nl FROM Bewoners LEFT OUTER JOIN CRIB ON CRIB.address_id = Bewoners.address_id WHERE LENGTH(Bewoners.Email) <3 GROUP BY CRIB.streetname_nl ) AS B ON B.streetname_nl = A.CRIBstreetname_nl ORDER BY 15 ASC, 18 ASC limit 0, 20

Now I can see the order by 15 asc, 18 asc in the output of php, but where is the order being set? So I added my own ORDER BY in the query above, same error.

What am I missing here?
D
 		DealerModulesDevClub member 2/28/2025

ChatGPT says:
The issue with your query appears to be related to the ORDER BY clause. The numbers 15 and 18 in the ORDER BY clause are not valid column references unless the columns in the SELECT list (from subqueries A and B) have a position corresponding to those numbers.

In MySQL, when you use numbers in the ORDER BY clause, they refer to the position of the columns in the SELECT list. Since your subqueries only select two columns each (MetEmailadres or ZonderEmailadres and CRIBstreetname_nl), the valid values for ORDER BY are 1 and 2. Trying to reference columns 15 and 18 causes the error.

It suggests:
SELECT *
FROM (
SELECT COUNT(Bewoners.id) AS MetEmailadres, CRIB.streetname_nl AS CRIBstreetname_nl
FROM Bewoners
LEFT OUTER JOIN CRIB ON CRIB.address_id = Bewoners.address_id
WHERE LENGTH(Bewoners.Email) > 3
GROUP BY CRIB.streetname_nl
) AS A
INNER JOIN (
SELECT COUNT(Bewoners.id) AS ZonderEmailadres, CRIB.streetname_nl
FROM Bewoners
LEFT OUTER JOIN CRIB ON CRIB.address_id = Bewoners.address_id
WHERE LENGTH(Bewoners.Email) < 3
GROUP BY CRIB.streetname_nl
) AS B
ON B.streetname_nl = A.CRIBstreetname_nl
ORDER BY A.MetEmailadres ASC, B.ZonderEmailadres ASC
LIMIT 0, 20;