How to call an SQL Order Function from one table as reference to another?

user_14637569 0
Viewed 20

I want to sort the column 'Name' from my table named 'People' using the SQL Order DESC referencing the column 'ReportID' in a separate table named 'Report' with the following execution:

cur.execute("""SELECT "Name" FROM People WHERE "ID" IN (SELECT "ID" FROM Report WHERE "ReportID" = ?) ORDER BY "Date" DESC""", (var,)

Instead, I get the error prompt: sqlite3.OperationalError: no such column: Date

Any insights on what I can do to be able to call Date properly? Both tables are from the same database.

sqlite database
1 Answers

The column date is only visible inside the subquery of the operator IN.

Join the tables and then you can use the column date in the ORDER BY clause:

SELECT DISTINCT p.Name 
FROM People p INNER JOIN Report r 
ON r.ID = p.ID 
WHERE r.ReportID = ? 
ORDER BY r.Date DESC

You can remove DISTINCT if there is no case of duplicate matching rows in the table Report.

user_104988280
Related