CODEWITHSUNDEEP
sqlsql-serverinner-join
Lernas
Lernas

Reputation: 61

SQL WHERE and AND not returning values

I have 3 tables:

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?

Upvotes: 2

Views: 790

Answers (4)

Esty
Esty

Reputation: 1912

And will never work because no row exists that have Hot or New together; I mean 1, 4, 10 have but in different rows.

SELECT * FROM
(
    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' 
    group by 
    n.ArticleID, n.Title, n.ArticleImage, cl.CategoryName
) AS A
INNER JOIN
(
    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 = 'New'
    group by 
    n.ArticleID, n.Title, n.ArticleImage, cl.CategoryName
) AS B ON A.ArticleID = B.ArticleID

For AND Operator AND means there should be a row with Category name 'New' AND 'Hot'. That is not possible ever. You have 2 rows for 1, 4 & 10 each but none of these 6 rows have category name 'New' and 'Hot' together.

For OR/IN Operator OR means to choose those rows having category name either 'Hot' or 'New' and now you got all rows as return value.

My Approach I just choose values with 'New' and 'Hot' differently and select their common parts by INNER JOIN. Nothing hard man. I think you should read more about SQL JOINS.

And yes, I go for the simplest way but you should follow @Gordon's smarter way.

Upvotes: 0

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Here is the method using CTE to get your result.

;with cte_1
 AS
(SELECT 
      n.ArticleID, n.Title, n.ArticleImage, cl.CategoryName
       ,COUNT(1) OVER(partition by   n.ArticleID, n.Title, n.ArticleImage, cl.CategoryName order by n.ArticleId) CNT
  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 IN ( 'Hot' ,'New')
 )
SELECT ArticleID, Title, ArticleImage, CategoryName
FROM cte_1
WHERE CNT =2

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93724

Certainly you don't understand the difference between AND and OR

AND - Implies that the conditions Before and After AND operator should satisfy. In your case it is impossible to have a single row with CategoryName both Hot and New.

OR - Implies that the conditions Before or After OR operator satisfies then return the row. In your case it returns the result.

But the correct way to do this would be using IN operator

where cl.CategoryName IN ( 'Hot' , 'New' )

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269933

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.

Upvotes: 4

Related Questions