sql query dependant column
I have two tables
one is keywords [id | keyword | account] k and one is templateKeywordLink [templateId | keywordId] tkl
What I want to do is get a result set with all keywords from the keywords table (where account = 1)
I also want to add another colum开发者_JAVA百科n called selected.
if k.id in (select templateId from templateKeywordLink)
then selected should contain "selected". else it should be null
Im wondering what the optimal query is to do this?
There are several ways to do this. One way is to do a left join against a query that returns the IDs of interest.
Note that most databases have more compact ways to do what I did with a CASE statement, but CASE is portable to any relational database you could want to use.
SELECT k.*
, CASE
WHEN tkl.id IS NULL
THEN NULL
ELSE 'selected'
END as selected
FROM keywords k
LEFT JOIN (
SELECT DISTINCT templateId
FROM templateKeywordLink
) as tkl
ON k.id = tkl.templateId
精彩评论