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
Brandon GarnettAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Vitor MontalvãoMSSQL Senior EngineerCommented:
Maybe is more readable if you use IN (ie, give me all records that have these values):
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')

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
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 PletcherSenior DBACommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.