MySQL query WHERE condition

Question

I have the table doTable in MySQL database. In doTable I have three fields:

IdCode  int 11
State   char 2
id      int 11

The values of field State for single IdCode could be: A, B, C, D or TC.

+---------+-------+----+
| IdCode  | State | id |
+---------+-------+----+
| 6027713 | TC    |  1 |
| 7057816 | B     |  2 |
| 7047412 | TC    |  3 |
| 7047412 | A     |  4 |
| 6027713 | B     |  5 |
| 6027713 | B     |  6 |
| 6027713 | C     |  7 |
| 6040309 | TC    |  8 |
| 6040309 | A     |  9 |
| 6040309 | TC    | 10 |
| 6040309 | A     | 11 |
| 4041208 | TC    | 12 |
| 7060912 | TC    | 13 |
| 7060912 | D     | 14 |
+---------+-------+----+

I need extract all rows of doTable where the State value are TC only when the same IdCode contains A, B, C or D.

I need this output:

+---------+-------+----+
| IdCode  | State | id |
+---------+-------+----+
| 6027713 | TC    |  1 |
| 7057816 | B     |  2 |
| 7047412 | TC    |  3 |
| 7047412 | A     |  4 |
| 6027713 | B     |  5 |
| 6027713 | B     |  6 |
| 6027713 | C     |  7 |
| 6040309 | TC    |  8 |
| 6040309 | A     |  9 |
| 6040309 | TC    | 10 |
| 6040309 | A     | 11 |
| 7060912 | TC    | 13 |
| 7060912 | D     | 14 |
+---------+-------+----+

And exclude in the output the row with ID number 12 because I don't have values A, B, C or D for the same IdCode

+---------+-------+----+
| IdCode  | State | id |
+---------+-------+----+
| 4041208 | TC    | 12 |
+---------+-------+----+

I have tried this query but Empty set.

mysql> SELECT
    *
FROM
    `dotable`
WHERE
    (
        State = 'A'
        OR State = 'B'
        OR State = 'C'
        OR State = 'D'
    )
AND State = 'TC';
Empty set

mysql>

Can you help me?

-- ----------------------------
-- Table structure for `dotable`
-- ----------------------------
DROP TABLE IF EXISTS `dotable`;
CREATE TABLE `dotable` (
  `IdCode` int(11) DEFAULT NULL,
  `State` char(2) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of dotable
-- ----------------------------
INSERT INTO `dotable` VALUES ('6027713', 'TC', '1');
INSERT INTO `dotable` VALUES ('7057816', 'B', '2');
INSERT INTO `dotable` VALUES ('7047412', 'TC', '3');
INSERT INTO `dotable` VALUES ('7047412', 'A', '4');
INSERT INTO `dotable` VALUES ('6027713', 'B', '5');
INSERT INTO `dotable` VALUES ('6027713', 'B', '6');
INSERT INTO `dotable` VALUES ('6027713', 'C', '7');
INSERT INTO `dotable` VALUES ('6040309', 'TC', '8');
INSERT INTO `dotable` VALUES ('6040309', 'A', '9');
INSERT INTO `dotable` VALUES ('6040309', 'TC', '10');
INSERT INTO `dotable` VALUES ('6040309', 'A', '11');
INSERT INTO `dotable` VALUES ('4041208', 'TC', '12');
INSERT INTO `dotable` VALUES ('7060912', 'TC', '13');
INSERT INTO `dotable` VALUES ('7060912', 'D', '14');

I mean that if you don't include this condition does not extract State = 'TC' — Antonio Mailtraq
– Antonio Mailtraq, Commented Jan 26, 2015 at 15:37

Raging Bull · Accepted Answer · 2015-01-27 10:03:22Z

1

Try this:

SELECT *
FROM `dotable`
WHERE IdCode IN 
    (SELECT IdCode 
     FROM `dotable`
     WHERE State IN ('A','B','C','D'))

Explanation:

Inner query selects the IdCodes which as atleast one state from the values A,B,C and D. Outer query selects all records which has IdCodes from the result of inner query.

Result:

IDCODE  STATE   ID
6027713 TC      1
7057816 B       2
7047412 TC      3
7047412 A       4
6027713 B       5
6027713 B       6
6027713 C       7
6040309 TC      8
6040309 A       9
6040309 TC      10
6040309 A       11
7060912 TC      13
7060912 D       14

See result in SQL Fiddle.

edited Jan 27, 2015 at 10:03

answered Jan 26, 2015 at 15:38

Raging Bull

18.8k13 gold badges52 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user359040 · Accepted Answer · 2015-01-26 15:38:44Z

0

Try:

select f.*
from dotable f
join (select distinct idcode
      from dotable 
      where state in ('A','B','C','D') ) x
on x.idcode = f.idcode

SQLFiddle Here.

answered Jan 26, 2015 at 15:38

user359040

Comments

jazZRo · Accepted Answer · 2015-01-26 16:08:34Z

0

The code below fill you´r needs

mysql> SELECT
    *
FROM
    `dotable`
WHERE
    (
        State = 'A'
        OR State = 'B'
        OR State = 'C'
        OR State = 'D'
        AND IdCode = (SELECT IdCode WHERE State = 'TC')
    )
;

edited Jan 26, 2015 at 16:08

jazZRo

1,6081 gold badge16 silver badges20 bronze badges

answered Jan 26, 2015 at 15:46

JPIN

11 bronze badge

Collectives™ on Stack Overflow

MySQL query WHERE condition

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related