Clever Geek Handbook

Comparing two values ​​in one column

I need to compare a value in one column in one table. Here's an example 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 to return a red value. Violet and violet should return blue. Puke green and purple must return zero.

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

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


source to share


5 answers


In addition to Beginner 's answer , it is possible to solve this problem without 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 (suggestion HAVING

) are the entries you want to keep.

Note. The number using the sentence HAVING

should match the number of colors you are looking for.

+2


source


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
+2


source 


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 a matching color table so you can make JOIN and more specific.

0


source


Try this using suggestion GROUP BY 

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;
0


source


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

Try the following:

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'
0


source






More articles:


All Articles