CODEWITHSUNDEEP
sql
Surjya Narayana Padhi
Surjya Narayana Padhi

Reputation: 7841

How to write this query to show result for one column based on specific values of another column?

My database schema is like following

table - X has following 3 columns docid(document id), terms(terms in document), count(number of terms of occurence of the term for specific docid)

docid
terms
count

How to write a query to find documents which contains both the words 'hello' and 'hi' in terms column?

Upvotes: 0

Views: 212

Answers (3)

Amit Singh
Amit Singh

Reputation: 8109

Try like this...

Select DocId,Count(term) from Table Name
where Term='hello' or Term='hi'
Group by DocId 
Having Count(Distinct term)=2;

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Try this:

SELECT docid, COUNT(term)
FROM tablex
WHERE term IN('hello', 'hi')
GROUP BY docid
HAVING COUNT(DISTINCT term) = 2;

See it in action:

Upvotes: 1

Skinny Pipes
Skinny Pipes

Reputation: 1035

Select DocId
FROM TableName
where Term IN ('hello','hi')
Group by DocId 
Having Count(*)=2;

a DISTINCT keyword in HAVING clause is much preferred if Term is not unique on every DocID

Select DocId
FROM TableName
where Term IN ('hello','hi')
Group by DocId 
Having Count(DISTINCT Term)=2;

Upvotes: 1

Related Questions