CODEWITHSUNDEEP
c#sqlite
user34537
user34537

Reputation:

sqlite How do i select using results from another select?

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.

Upvotes: 2

Views: 6131

Answers (3)

Alex Martelli
Alex Martelli

Reputation: 881745

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.

Upvotes: 6

Jay
Jay

Reputation: 57939

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.

Upvotes: -1

Artyom
Artyom

Reputation: 31243

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

Upvotes: 2

Related Questions