June 30, 2011 at 6:49 am
There is a table as below. I want to select all product which has two color: RED and GREEN.
The final result should be AAA and CCC
How to code it?
PRODUCT-----COLOR
---------------------
AAA----------RED
AAA----------GREEN
AAA----------YELLOW
AAA----------BLUE
BBB-----------GREEN
CCC----------RED
CCC----------GREEN
CCC----------BLUE
DDD----------BLACK
DDD----------RED
DDD----------YELLOW
June 30, 2011 at 6:53 am
either of these will do what you asked...they are functionally the same i think.
WHERE COLOR='RED' OR COLOR='GREEN'
WHERE COLOR IN('RED','GREEN')
now if you want where the product has BOTH, you have to joint he table against itself
...
TABLENAME T1
INNER JOIN TABLENAME T2
ON T1.PRODUCT = T2.PRODUCT
WHERE t1.COLOR = 'RED'
AND t2.COLOR = 'GREEN'
Lowell
June 30, 2011 at 6:58 am
No, either way is for "Or" only.
What I need is for "And". The color must be "RED" and "GREEN"
June 30, 2011 at 7:00 am
Sorry, I do not see second step. I'll try it.
Thank you
June 30, 2011 at 7:00 am
is this what your looking for?
select
distinct product
from @colour c1
where
(select COUNT(*) from @colour c2 where color = 'GREEN' and c2.product = c1.product) > 0
and (select COUNT(*) from @colour c3 where color = 'RED' and c3.product = c1.product) > 0
June 30, 2011 at 7:47 am
DECLARE @data TABLE
(
ProductCHAR(3) NOT NULL,
ColourVARCHAR(10) NOT NULL
);
INSERT @data
(Product, Colour)
VALUES
('AAA', 'RED'),
('AAA', 'GREEN'),
('AAA', 'YELLOW'),
('AAA', 'BLUE'),
('BBB', 'GREEN'),
('CCC', 'RED'),
('CCC', 'GREEN'),
('CCC', 'BLUE'),
('DDD', 'BLACK'),
('DDD', 'RED'),
('DDD', 'YELLOW');
SELECT
d.Product
FROM @data AS d
GROUP BY
d.Product
HAVING
SUM
(
CASE
WHEN d.Colour = 'RED' THEN 1
WHEN d.Colour = 'GREEN' THEN 2
ELSE 0
END
) = 3
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply