Query to find miss matched categories?

Transact-SQL
1

Hello I have a database that I'm trying to look for artist with the same name and not matching category tags. This is my query:

 SELECT  Artist, Category
 FROM     Products
 WHERE  (Artist = Artist) AND (Category <> Category)

That brings up nothing. What do I need to do to show results that I'm looking for any help would be great.

Thanks

2 
SELECT  Artist, Category
FROM     Products p1
WHERE EXISTS(
    SELECT 1 FROM Products p2
    WHERE p2.Artist = p1.Artist AND p2.Category <> p1.Category
    )
3

hi

i know scott pletcher has already given the solution

i have a different approach
hope it helps :slight_smile: :slight_smile:

is it simpler worse better performance wise etc
is a different discussion

drop create data ... 
drop table Products 
go 

create table Products
(
Artist varchar(100),
Category varchar(100)
)
go 


insert into Products select  'chaka khan','POP'
insert into Products select  'chaka khan','Blues'
insert into Products select  'Popsy','Rythm and Blues'
insert into Products select  'Popsy','Country'
GO 


select * from Products
go
SQL .. a different solution 
SELECT  a.Artist, a.Category
 FROM     Products a join Products b
 on  a.Artist = b.Artist AND a.Category <> b.Category

image

4

This works, one last question how can I have it ignore anything category that is 18? I tried this, but still shows things with 18 as category.

SELECT DISTINCT Artist
FROM Products AS p1
WHERE EXISTS
(SELECT 1 AS Expr1
FROM Products AS p2
WHERE (Artist = p1.Artist) AND (Category <> p1.Category) AND (NOT (Category = '18')))
ORDER BY Artist

5

hi

you will need to have something like this

( select * from products where category <> '18)
join

i hope this makes sense
:slight_smile: :slight_smile:

6 
SELECT DISTINCT Artist
FROM Products AS p1
WHERE EXISTS
(SELECT 1 AS Expr1
FROM Products AS p2
WHERE (Artist = p1.Artist) AND (Category <> p1.Category) AND (NOT (Category = '18'))) AND
    Category <> '18' --<<-- add this condition
ORDER BY Artist
7

The problem with a JOIN vs an EXISTS check is that, if you get an artist with 3 (or more) categories, you list the same row more than once:

;with products as (
    select * from ( values('same_artist', 'genre1'),('same_artist', 'genre2'),
        ('same_artist', 'genre3') ) as data(artist, category)
)
SELECT  a.Artist, a.Category
 FROM     Products a join Products b
 on  a.Artist = b.Artist AND a.Category <> b.Category