[Query] ..WHERE IN (.. WHERE IN ...(...))

coldwerturkey

Somthing like this:

SELECT * FROM `Content` 
	WHERE `ContentID` IN (
		SELECT `ContentID` FROM `Page` 
		WHERE `PageID` IN (
			SELECT `PageID` FROM `DocumentPage` WHERE `DocumentID` = '86'
		)
	)
 ORDER BY `DateCreated` DESC

does not work. I worked on it for an hour making sure there were no typos or wrong logic, but in unprofessional-uneducated-theory, this should work right? If not, is this where one would use JOIN? or something like that?

Thank you in advance

johanafm

That query should indeed work. What error message do you get?

Just be aware that subqueries can be slower than performing WHERE field IN (list, of, values). Join operations have the possibility of being more efficient as well since they give the query optimizer more execution plan options. I.e. in what order to read the tables.

So you are correct in assuming that you can achieve this by join operations

SELECT *
FROM Content AS C
	INNER JOIN Page AS P ON C.ContentID = P.ContentID
	INNER JOIN DocumentPage AS DP ON DP.PageID = P.PageID
WHERE DocumentID = 86
ORDER BY DateCreated

Unless 86 is stored as text, there is no reason to quote it.

Also, you don't have to encase identifiers in acute accents, unless they happen to be reserved words, such as key. You are of course free to do so, but in my opinion it reduces readability.