I'm not getting the expected result from an SQL query.

Question

I'm developing a search function for a website. I have a table called keywords with two fields id and keyword. I have two separate search queries for AND and OR. The problem is with the AND query. It is not returning the result that I expect.

The printed SQL is :

SELECT COUNT(DISTINCT tg_id) 
FROM tg_keywords 
WHERE tg_keyword='keyword_1' 
  AND tg_keyword='keyword_2'

The count returned is 0, while if I perform the same SQL with OR instead of AND the count returned is 1. I expected the count to be 1 in both cases, and I need it to be this way as the AND results will take priority over the OR results.

Any advice will be much appreciated.

Thanks Archie

Answer 1

Hi,

your query can't work because you have a condition which is always false so no record will be selected!

tg_keyword='keyword_1' AND tg_keyword='keyword_2'

what are you trying to do? Could you post the columns of this table?

Answer 2

I don't know what your table looks like and how things are related to each other, but this query makes no sense. You're returning rows where the keyword is one thing and another thing at the same time? That's impossible.

You probably have another table that links to the keywords? You should search with that, using a join, and search for both keywords. We could give you a more precise answer if you could tell us what your tables look like.

EDIT: Based on what you wrote in a comment below (please edit your question!!), you're probably looking for this:

SELECT COUNT(DISTINCT tg_id) 
FROM tg_keywords AS kw1, tg_keywords AS kw2
WHERE kw1.tg_id = kw2.tg_id
  AND kw1.tg_keyword='keyword_1'
  AND kw2.tg_keyword='keyword_2'

Answer 3

tg_keyword='keyword_1' AND tg_keyword='keyword_2'

Logically this cannot be true, ever. It cannot be both. Did you mean something like:

SELECT * FROM keywords
WHERE tg_keyword LIKE '%keyword_1%' OR tg_keyword LIKE '%keyword_2%'
ORDER BY tg_keyword LIKE '%keyword_1%' + tg_keyword LIKE '%keyword_2%' DESC;

Answer 4

It will always return 0, unless keyword_1=keyword_2. tg_keyword can only have one value, and when you say AND, you're asking for both conditions to be true.

It's the same, logically speaking, as asking "How many friends do I have whose name is 'JACK' and 'JILL'"? None, nobody is called both JACK and JILL.

Answer 5

I think you will need to join tg_keywords to itself. Try playing around with something like

select *
from tg_keywords k1
join tg_keywords k2 on k1.tg_id = k2.tg_id
where k1.tg_keyword = 'keyword_1' and k2.tg_keyword = 'keyword_2'

Answer 6

Based on the OP's clarification:

I have a table with multiple keywords with the same id. How can I get more than one keyword compared for the same id, as the search results need to be based on how many keywords from a search array match keywords in the keywords table from each unique id. Any ideas?

I assume you're looking to return search results based on a ranking of how many of the selected keywords are a match with those results? In other words, is the ID field that multiple keywords share the ID of a potential search result?

If so, assuming you pass in an array of keywords of the form {k1, k2, k3, k4}, you might use a query like this:

SELECT ID, COUNT(ID) AS ResultRank FROM tg_keywords WHERE tg_keyword IN (k1, k2, k3, k4) GROUP BY ID ORDER BY ResultRank DESC

This example also assumes a given keyword might appear in the tables multiple times with different IDs (because a keyword might apply to multiple search results). The query will return a list of IDs in descending order based on the number of times they appear with any of the selected keywords. In the given example, the highest rank for a given ID should be 4, meaning ALL keywords apply to the result with that ID...

Answer 7

Try:

SELECT tg_id
FROM tg_keywords 
WHERE tg_keyword in ('keyword_1','keyword_2')
GROUP BY tg_id
HAVING COUNT(DISTINCT tg_keyword) = 2