CODEWITHSUNDEEP
mysqlsql
Nick Shears
Nick Shears

Reputation: 1133

MySQL - Select where all rows sharing the same id, each contain a column equal to null

Bit tricky trying to word this one, I'll start by giving an example

Table name: tags

artist_id(int) | tag_id(int) | main_tag(NULL by default)

The rows can contain multiple different tags for each artist and one of them needs to be set as a main tag.

So I'm trying to find out all the artists that do not currently have a main tag set in one of the rows.

Example:

artist_id | tag_id | main_tag
 2203         4         NULL
 2203         53         1
 2203         32        NULL

I don't want to retrieve the artist id here as a main tag has been set for this artist.

Whereas:

artist_id | tag_id | main_tag
 1333         23       NULL
 1333         44       NULL

I want to get this artist_id as all the main_tag values are NULL.

I'm stuck on how to put this into an SQL statement, will really appreciate any help :)

Upvotes: 0

Views: 154

Answers (2)

AMADANON Inc.
AMADANON Inc.

Reputation: 5919

So lets break this down:

The artists you want to ignore are returned by the following query:

SELECT artist_id 
FROM   tags 
WHERE  main_tag IS NOT NULL

So the ones you DO want are the ones where the artist_id is not in the above list:

SELECT artist_id, 
       tag_id 
FROM   tags 
WHERE  artist_id NOT IN (SELECT artist_id 
                         FROM   tags 
                         WHERE  main_tag IS NOT NULL);

If you want just the artist_id, and you want each artist to appear only once, then you can remove the reference to tag_id, and group them by artist:

SELECT artist_id 
FROM   tags 
WHERE  artist_id NOT IN (SELECT artist_id 
                         FROM   tags 
                         WHERE  main_tag IS NOT NULL) 
GROUP  BY artist_id;

Hint: if you want to post something database-ish, you can build your tables in sqlfiddle, then post the link, which gives people a starting point. I did this, the link is http://sqlfiddle.com/#!2/db53d/2

Upvotes: 3

Jason Heo
Jason Heo

Reputation: 10236

Following queries produce as same as @AMADNON Inc.'s query result.

A. GROUP BY and HAVING

SELECT artist_id
FROM tags
GROUP BY artist_id
HAVING SUM(main_tag IS NULL) = COUNT(*);

B. INNER JOIN

SELECT DISTINCT t1.artist_id
FROM tags t1 INNER JOIN (
    SELECT artist_id 
    FROM tags WHERE main_tag IS NOT NULL
  ) t2 ON t1.artist_id != t2.artist_id;

C. NOT EXISTS

SELECT DISTINCT artist_id
FROM tags t1 
WHERE NOT EXISTS (
    SELECT 1 
    FROM tags
    WHERE main_tag IS NOT NULL 
        AND artist_id = t1.artist_id
);

It would be much better if you let me know the performance results for each queries if you have a lot of data.

Upvotes: 0

Related Questions