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.

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'

Open in new window

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

Vitor Montalvão

If you want both then you need to use OR instead of AND since you can't have the same values at same time in a record:

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' OR tblKeywords.keyword LIKE 'keyword2'

Open in new window

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

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.

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
              )

Open in new window

Scott Pletcher

SELECT tblSources.SourceID FROM tblSources
INNER JOIN tblSource2Keyword ON tblSource2Keyword.SourceID = tblSources.SourceID
INNER JOIN tblKeywords ON tblSource2Keyword.KeywordID = tblKeywords.KeywordID
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