CODEWITHSUNDEEP
mysqlsql
user3179585
user3179585

Reputation: 123

Compare Two Values in Same Column

I need to compare the value in a single column in a single table. Here is a sample table:

ID  Cat     Color
======================
1   red     maroon
2   red     orange
3   red     pink
4   blue    violet
5   blue    purple
6   blue    indigo
7   green   puke green
8   green   hunter green

I am given 2 colors from the Color column. I need to know if they belong to the same Cat column. For example, I will be given maroon and orange. I need the value red returned. Violet and purple should return blue. Puke green and violet should return null.

So far I have the following SQL but it's not exactly what I am looking for, especially with the Limit 1. I am looking for a single query to return Cat field without using Limit 1.

SELECT Cat
From foo
WHERE 
Color = 'maroon'
and
Color = 'orange'
LIMIT 1

Upvotes: 4

Views: 27705

Answers (5)

Mr. Llama
Mr. Llama

Reputation: 20889

In addition to Beginner's answer, it's possible to solve this problem without a GROUP_CONCAT:

SELECT cat
FROM foo
WHERE color IN ('maroon', 'orange')
GROUP BY cat
HAVING COUNT(*) = 2
;

This works by selecting all cats with the specified colors. When we group them, the cats that appear multiple times (the HAVING clause) are the records you want to keep.

Note: the number using the HAVING clause should match the number of colors you're searching for.

Upvotes: 3

Abhishekh Gupta
Abhishekh Gupta

Reputation: 6236

You can try this:

SELECT x.cat 
FROM (
  SELECT cat, GROUP_CONCAT(color) AS colors
  FROM tablename
  GROUP BY cat) AS x
WHERE FIND_IN_SET('maroon', x.colors) > 0
  AND FIND_IN_SET('orange', x.colors) > 0

Edit 1: Another Alternative

SELECT IF(
          FIND_IN_SET('maroon', GROUP_CONCAT(color)) > 0 
          AND FIND_IN_SET('orange', GROUP_CONCAT(color)) > 0 , cat, NULL
       ) AS cat
FROM tablename
GROUP BY cat

Upvotes: 2

MartianCodeHound
MartianCodeHound

Reputation: 380

DECLARE @Cat varchar(50) = null

select @Cat = Cat
from foo f1
JOIN foo f2 on f1.Cat = f2.Cat
where f1.Color = 'maroon'
and f2.Color = 'orange'

select @Cat

Ideally you should have an associated table of colors so that you can do a JOIN and a more specific where.

Upvotes: 0

Rahul
Rahul

Reputation: 77856

Try like this using GROUP BY clause

select case when count(*) > 0 then cat else NULL end as MyCat_Column
from (
select Cat
from table1
where color in ('maroon','orange')
group by cat
having count(distinct color) >= 2
) tab;

Upvotes: 0

Hyder
Hyder

Reputation: 1173

You need to JOIN the table with itself, on the primary keys of the table (also called Self-Join).

Try this:

SELECT A.Cat
From foo A, foo B
WHERE 
A.ID = B.ID 
and A.Cat = B.Cat and
A.Color = 'maroon'
and
B.Color = 'orange'

Upvotes: 0

Related Questions