what is sql query of below table?

CRE开发者_StackOverflowATE TABLE table1(kid char(2),color varchar(9));

INSERT INTO table1('k1'.'yello');
INSERT INTO table1('k1'.'red');
INSERT INTO table1('k2'.'yello');
INSERT INTO table1('k2'.'blue');
INSERT INTO table1('k3'.'yello');

Q: Display kid of table1 which has color values yellow and red (both of them)?

What is sql query?

Use:

  SELECT t.kid
    FROM TABLE1 t
   WHERE t.color IN ('yellow', 'red')
GROUP BY t.kid
  HAVING COUNT(DISTINCT t.color) = 2

• The IN clause will get only records whose color values are either yellow or red
• The GROUP BY is necessary to remove duplicates
• COUNT(DISTINCT t.color) = 2 ensures valid kid values will be returned. Without the DISTINCT, two yellows/etc would satisfy the COUNT check

SELECT kid 
  FROM table1 as t, 
       table1 as t2 
 WHERE t1.color = 'yellow' 
   AND t1.kid = t2.kid 
   AND t2.color = 'red'