sqlite How do i select using results from another select?

Question

in SQLite with C# I want to do

SELECT * FROM media WHERE mediaId= for each
  SELECT mediaId FROM fav WHERE userId=1 ORDER BY indx DESC LIMIT 0, 100

The syntax is obviously incorrect. I have no idea how to use the param from one select statement in another.

Answer 1

You need there join statements:

SELECT * FROM fav 
WHERE fav.userId=1
LEFT JOIN media 
ON media.mediaId=fav.mediaID
ORDER BY indx DESC LIMIT 0, 100

Answer 2

You need a SQL join statement. This would be standard T-SQL, not at all specific to SQLite.

You'd do something like this:

SELECT m.* FROM media AS m JOIN fav AS f ON m.mediaId = f.mediaID WHERE f.userId = 1

You can add your ordering requirements on top of this.

Answer 3

Most direct translation of your invalid syntax:

SELECT * FROM media WHERE mediaId IN
  (SELECT mediaId FROM fav WHERE userId=1)
ORDER BY indx DESC LIMIT 0, 100

A better style (maybe more speedy, but I haven't measured:

SELECT media.* FROM media
JOIN fav USING (mediaId)
WHERE fav.userId=1 
ORDER BY media.indx DESC LIMIT 0, 100

Both of these are standard SQL, btw, and the second form in particular will tend to work well on just about any relational DB implementation.