CODEWITHSUNDEEP
mysqlsql
Phil
Phil

Reputation: 14671

Complex SQL Querying: Two queries within the same table?

I have a table like:

ID  |     LABEL     |   SOME_VALUE
1           a              rand_1
2           a              NULL
3           b              rand_9
4           c              rand_3
5           c              rand_3
6           c              rand_3
7           d              NULL
8           d              rand_4

As you can see, ID is unique, label is not unique (can be 1 or more) and some_value is also not unique.

What I want to do is the following:

I want to get a unique list of LABELS, which exist in the database in more than one rows (min 2) and of which rows has SOME_VALUE not NULL.

So I would get: 

ID  |     LABEL     |   SOME_VALUE
1           a            rand_1
2           a            NULL
7           d            NULL
8           d            rand_4

in return.

How can I achieve this?

Upvotes: 0

Views: 117

Answers (4)

user1191247
user1191247

Reputation: 12973

This should work -

SELECT test.*
FROM (
    SELECT label
    FROM test
    GROUP BY Label
    HAVING COUNT(DISTINCT IFNULL(some_value, '~null~')) > 1
) AS tmp
INNER JOIN test
    ON tmp.label = test.label;

Upvotes: 1

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

There are two versions. First one does exactly as listed in results, eliminating rand_3 because even though it appears three times all the values are the same (I don't see distinct condition specified in question).

There must be a better way, but as they say I can't brain today, I have the dumb :-)

select * 
from tbl
inner join
(
  select label
  FROM tbl
  GROUP BY Label
  HAVING count (distinct some_value) 
       + sum(distinct case when some_value is null then 1 else 0 end) > 1
) a
on tbl.label = a.label

Second one retrieves C also following the requirements (some_value being not null for at least one of some_value).

select * 
from tbl
inner join
(
  select label
  FROM tbl
  GROUP BY Label
  HAVING count(*) > 1 and count(some_value) > 0
) a
on tbl.label = a.label

And there is Sql Fiddle.

Upvotes: 2

Konerak
Konerak

Reputation: 39773

SELECT t1.* 
FROM yourTable t1
JOIN yourTable t2
ON t1.LABEL = t2.LABEL
AND t1.ID < t2.ID
WHERE t1.SOME_VALUE IS NOT NULL
OR t2.SOME_VALUE IS NOT NULL

Upvotes: 1

Paul Grimshaw
Paul Grimshaw

Reputation: 21034

The HAVING parameter limits grouped items:

SELECT 
    Label
FROM dbo.TableName
WHERE NOT Some_Value IS NULL
GROUP BY Label
HAVING COUNT(*) > 2

Upvotes: 2

Related Questions