SQLite not returning result when using AND

Question

I am having a very puzzling question. When I execute the following query:

SELECT DISTINCT posts.pid FROM posts 
INNER JOIN questions 
ON questions.pid = posts.pid 
INNER JOIN tags 
ON tags.pid = posts.pid 
WHERE tags.tag = 'Relational' COLLATE NOCASE;

Where it select posts with the tag "Relational", it is able to return the id of: 001, 002, 014, 015. Now when I execute the query that extracts another tag like this:

SELECT DISTINCT posts.pid FROM posts 
INNER JOIN questions 
ON questions.pid = posts.pid 
INNER JOIN tags 
ON tags.pid = posts.pid 
WHERE tags.tag = 'Database' COLLATE NOCASE;

It is able to return 001, 002, 003, 009, 015, 022. It should be clear that id 001, 002 and 015 are the overlapping ids, and therefore when I use the AND operator it should be my expected return. However when I execute the following query:

SELECT DISTINCT posts.pid FROM posts 
INNER JOIN questions 
ON questions.pid = posts.pid 
INNER JOIN tags 
ON tags.pid = posts.pid 
WHERE tags.tag = 'Relational' COLLATE NOCASE
AND tags.tag = 'Database' COLLATE NOCASE;

I get an empty result. I have tried a lot of things including adding brackets around the conditions and the AND clause, I cannot fix this problem. Anyone knows what is the cause?

Gordon Linoff · Accepted Answer · 2020-10-06 00:22:08Z

1

You want aggregation and having:

SELECT q.pid
FROM questions q
     tags t
     ON t.pid = q.pid
WHERE t.tag IN ('Relational' COLLATE NOCASE, 'Database' COLLATE NOCASE)
GROUP BY q.pid
HAVING COUNT(*) = 2;

If you are only trying to get the post id, there is no need to join in the posts table.

The version with AND cannot work. The WHERE clause works only on one row at a time and the tag cannot have two values in the same row.

answered Oct 6, 2020 at 0:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLite not returning result when using AND

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related