Lernas Lernas - 2 months ago 6
SQL Question

SQL WHERE and AND not returning values

I have 3 tables:


  • News (ArticleID, Title, ArticleImage)

  • Categories (EntryID, ArticleID, CategoryID, CategoryName)

  • CategoryList (CategoryID, CategoryName)



Bold: PK, Italic: FK

What I want to achieve: select rows of table
News
that belong to
CategoryName
'Hot' and 'New'.

Here's my query:

select
n.ArticleID, n.Title, n.ArticleImage, cl.CategoryName
from
News as n
inner join
Categories as c on n.ArticleID = c.ArticleID
inner join
CategoryList as cl on cl.CategoryID = c.CategoryID
where
cl.CategoryName = 'Hot'
and cl.CategoryName = 'New'
group by
n.ArticleID, n.Title, n.ArticleImage, cl.CategoryName


However, it does not return anything, but it should, since I have entries in the table
News
that belong to both categories 'New' and 'Hot'.

If instead of

cl.CategoryName = 'Hot' and cl.CategoryName = 'New'


I use

cl.CategoryName = 'Hot' or cl.CategoryName = 'New'


It returns those that belong to category 'Hot' plus those who belong to category 'New'.

OR result, IN(,) result
NOTE: 'Novidade' is what i refer to 'New'
I want to return 1, 4, 10.

So why or works, but and does not? How can I make and work?

Answer

Of course not. The CategoryName cannot be two things at once. If you want new articles that are in both groups, here is one way:

select n.ArticleID, n.Title, n.ArticleImage
from News n inner join
     Categories c
     on n.ArticleID = c.ArticleID inner join
     CategoryList cl
     on cl.CategoryID = c.CategoryID
where cl.CategoryName in ('Hot', 'New')
group by n.ArticleID, n.Title, n.ArticleImage
having count(distinct cl.CategoryName) = 2;

Note that the CategoryName has been removed from the select and group by.

The where clause gets articles that have the two categories. The having checks that both are on the article.

Comments