All geek questions in one place

A query that works in MYSQL but not SQLite (difference in syntax?)

Hi I have a query that selects all the houses from my table of houses that are grouped by street address. He counts how much is on this street, but also counts how many on this street are referenced in another table.

I have a query that works in MYSQL, but when I try to use it in SQlite in my iOS application, it does not work. Are there any syntax differences between the two that I don't know about?

SELECT haddress AS hd, COUNT( * ) , ( SELECT COUNT( * ) FROM canvass, house WHERE canvass.hid = house.hid AND house.haddress = hd ) FROM house GROUP BY haddress
+4
source share
1 answer

It seems you cannot refer to column aliases in internal SQLite queries, so you will have to slightly modify the internal query to use the alias for house so that you can reference the external house.haddress . The modified query should work on both SQLite and MySQL;

 SELECT haddress AS hd, COUNT( * ), ( SELECT COUNT( * ) FROM canvass, house house2 WHERE canvass.hid = house2.hid AND house2.haddress = house.haddress ) FROM house GROUP BY haddress
+5
source

Source: https://habr.com/ru/post/1496311/

More articles:


All Articles