asked on
Querying SQL Server 2012
I have a database with 3 tables tblSources, tblSource2Keyword, and tblKeywords. I'm trying to query the server to select all of the sources that have tblKeywords.keyword1 AND tblkeyword.keyword2, using the following statement.
But I keep getting an empty result set and I have verified that the source is associated with the 2 keywords. What is the correct syntax to achieve this?
Let me know if you need more information.
Thanks
SELECT tblSources.SourceID FROM tblSources
LEFT JOIN tblSource2Keyword ON tblSource2Keyword.SourceID = tblSources.SourceID
LEFT JOIN tblKeywords ON tblSource2Keyword.KeywordID = tblKeywords.KeywordID
WHERE tblKeywords.keyword LIKE 'keyword' AND tblKeywords.keyword LIKE 'keyword2'
But I keep getting an empty result set and I have verified that the source is associated with the 2 keywords. What is the correct syntax to achieve this?
Let me know if you need more information.
Thanks
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
Last Comment
Scott Pletcher
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Those should be INNER JOINS (if a match is found any unmatched records will be ignored anyway).
This is an often overlooked factor when using OUTER joins, if the outer joined tables are referenced in the where clause you frequently find that the impact is the equivalent of an inner join.
IF you really DID need an outer join to be effective then you would need to permit NULLS, e.g.
This is an often overlooked factor when using OUTER joins, if the outer joined tables are referenced in the where clause you frequently find that the impact is the equivalent of an inner join.
IF you really DID need an outer join to be effective then you would need to permit NULLS, e.g.
SELECT tblSources.SourceID FROM tblSources
LEFT JOIN tblSource2Keyword ON tblSource2Keyword.SourceID = tblSources.SourceID
LEFT JOIN tblKeywords ON tblSource2Keyword.KeywordID = tblKeywords.KeywordID
WHERE ( tblKeywords.keyword IN ('keyword','keyword2')
OR tblKeywords.keyword IS NULL
)
SELECT tblSources.SourceID FROM tblSources
INNER JOIN tblSource2Keyword ON tblSource2Keyword.SourceID
INNER JOIN tblKeywords ON tblSource2Keyword.KeywordI
WHERE tblKeywords.keyword LIKE 'keyword' OR tblKeywords.keyword LIKE 'keyword2'
GROUP BY tblSources.SourceID
HAVING SUM(CASE WHEN tblKeywords.keyword LIKE 'keyword' THEN 1 ELSE 0 END) = 1 AND
SUM(CASE WHEN tblKeywords.keyword LIKE 'keyword2' THEN 1 ELSE 0 END) = 1
INNER JOIN tblSource2Keyword ON tblSource2Keyword.SourceID
INNER JOIN tblKeywords ON tblSource2Keyword.KeywordI
WHERE tblKeywords.keyword LIKE 'keyword' OR tblKeywords.keyword LIKE 'keyword2'
GROUP BY tblSources.SourceID
HAVING SUM(CASE WHEN tblKeywords.keyword LIKE 'keyword' THEN 1 ELSE 0 END) = 1 AND
SUM(CASE WHEN tblKeywords.keyword LIKE 'keyword2' THEN 1 ELSE 0 END) = 1
Open in new window