CODEWITHSUNDEEP
mysql
Antonio Mailtraq
Antonio Mailtraq

Reputation: 1407

MySQL query WHERE condition

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');

Upvotes: 2

Views: 66

Answers (3)

Raging Bull
Raging Bull

Reputation: 18767

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.

Upvotes: 1

JPIN
JPIN

Reputation: 1

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')
    )
;

Upvotes: 0

user359040
user359040

Reputation:

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.

Upvotes: 0

Related Questions