Andrew Drummond wrote:
>
> for each element1 in (select address.* from numbers,address where
> numbers.number = "12345678" and numbers.address_id = address.address_id)
> {
> for each element2 in (select numbers.* from numbers where address_id =
> element1.address_id LIMIT 20)
> return element1.* , elemen2.number
> }
>
>
> the output would be
>
> 1 Peter 12345678
> 1 Peter 09876654
> 2 Paul 12345678
>
The following query will produce the output above, but it does not
implement the limit of 20 numbers per address that is shown in your
pseudo code.
select a.address_id, a.name, n.number
from address as a
join numbers as n on n.address_id = a.address_id
where a.address_id in
(select address_id from numbers where number = '12345678')
order by a.address_id;
I am still working on a complete query, but I have managed to trigger a
crash in SQLite in the process.
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
