Re: [sqlite] Indexing and Search speed
From: Dennis Cote <[EMAIL PROTECTED]> > No, that's not true. A sub-query is like any other query. I have > rearranged the query to make it more readable. > > select types.Track,types.URL > from ALBUM > inner join (select * from MUSIC where Artist_Id =?) as types > on ALBUM.AlbumId=types.Album_Id > order by ALBUM.YomiAlbumName; > > In this case the sub-query is returning the subset of the Music table by > the specified artist. This result table is given the name "types", and > it is joined to the album table. I didn't know this was possible -- my trusty "learn SQL in 10 minutes" has failed me. But it makes sense from your explanation. Thanks. In most cases like this, I've 'hidden' the join inside a view so the syntax is clean. But, I expect moving the filtering before the join is much more efficient, no? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexing and Search speed
dcharno wrote: >> select types.Track,types.URL from ALBUM inner join (select * from MUSIC >> where Artist_Id =?) types on ALBUM.AlbumId=types.Album_Id order by >> ALBUM.YomiAlbumName ; > > How does the subquery work in this statement? I thought subqueries > could only retrieve a single column. No, that's not true. A sub-query is like any other query. I have rearranged the query to make it more readable. select types.Track,types.URL from ALBUM inner join (select * from MUSIC where Artist_Id =?) as types on ALBUM.AlbumId=types.Album_Id order by ALBUM.YomiAlbumName; In this case the sub-query is returning the subset of the Music table by the specified artist. This result table is given the name "types", and it is joined to the album table. It is often the case the you use a sub-query to select a single result value (i.e. one row and one column) for use in a comparison, but there is no restriction on what they can return. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexing and Search speed
> select types.Track,types.URL from ALBUM inner join (select * from MUSIC > where Artist_Id =?) types on ALBUM.AlbumId=types.Album_Id order by > ALBUM.YomiAlbumName ; How does the subquery work in this statement? I thought subqueries could only retrieve a single column. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
