Selecting data from multiple rows

Question

For example test column 7 contains two rows, if the number column contains values 5 AND 6, AND the value is NOT X in the chr column, I would like to select select the rows with 7 in the test column.
For example test column 10 contains three rows, if the number column contains values 5 AND 6, AND the value X exists in the chr column, I would like to exclude rows with 10 in the test column.

The Demo of the below Schema and broken SQL query is available on SQL fiddle.

Schema:

CREATE TABLE TEST_DATA (ID INT, TEST INT, CHR VARCHAR(1), NUMBER INT);

INSERT INTO TEST_DATA VALUES 
( 1    ,    7 , 'C'   ,       5),
( 2    ,    7 , 'T'   ,       6),
( 3    ,    8 , 'C'   ,       4),
( 4    ,    8 , 'T'   ,       5),
( 5    ,    9 , 'A'   ,       4),
( 6    ,    9 , 'G'   ,       5),
( 7    ,   10 , 'T'   ,       4),
( 8    ,   10 , 'A'   ,       5),
( 9    ,   10 , 'X'   ,       6),
(10    ,   14 , 'T'   ,       4),
(11    ,   14 , 'G'   ,       5);

SQL:

SELECT *
FROM test_data t1, test_data t2
WHERE t1.number=5 is not t1.chr=X AND
      t2.number=6 is not t2.chr=X;

How would it be possible to keep test column if number columns contains 5 and 6 and the chr column does not contain X?

UPDATE As result it should only be test column with 7, because test column 7 have 5 and 6 in the number column and not X.

UPDATE 2 Result example:

ID | TEST | CHR  |  NUMBER               
1  |  7   |  C   |    5        
2  |  7   |  T   |    6

the number column contains values 5 AND 6 - this is not possible and would always return an empty result, since the same column in one row can not contain both 5 AND 6. You probably mean OR? — Andreas Fester
– Andreas Fester, Commented Oct 14, 2014 at 7:14

Strawberry · Accepted Answer · 2014-10-14 11:29:38Z

1

If I understand the requirement correctly...

SELECT a.test 
  FROM test_data a
  LEFT
  JOIN test_data b
    ON b.test = a.test 
   AND b.chr = 'x'
 WHERE a.number IN (5,6)
   AND b.id IS NULL
 GROUP
    BY a.test
HAVING COUNT(*) = 2;

http://www.sqlfiddle.com/#!2/1939f/4

You can join this result back on to test_data to get all results with a test equal to 7

answered Oct 14, 2014 at 11:29

Strawberry

34k14 gold badges43 silver badges57 bronze badges

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

Comments

Ullas · Accepted Answer · 2014-10-14 07:59:08Z

0

Try this.

Query

SELECT *
FROM test_data
WHERE number IN (5,6) 
AND test NOT IN (10)
AND chr NOT IN ('X');

Fiddle Demo

edited Oct 14, 2014 at 7:59

answered Oct 14, 2014 at 7:12

Ullas

11.6k5 gold badges36 silver badges52 bronze badges

5 Comments

user977828 Over a year ago

almost, it should only be test column with 7, because test column 7 have 5 and 6 in the number column and not X.

Ullas Over a year ago

@user977828:Updated the answer

user977828 Over a year ago

Thank you, but 8, 9 and 14 out of test column appears in the result. How would it be possible to get only test column 7 in a not static way like AND test NOT IN (8, 9, 10, 14)?

Ullas Over a year ago

@user977828: instead of test NOT IN (10), add test IN (7).

user977828 Over a year ago

However, in a big query I would not that e. g. test column 7 would fulfill the requirements. Is it a way to do it more dynamic?

Ullas · Accepted Answer · 2014-10-14 09:22:05Z

0

You want it like this ?

Query

SELECT *
FROM test_data
WHERE ID NOT IN 
(
   SELECT ID 
   FROM test_data 
   WHERE CHR='X' 
   AND NUMBER IN (5,6)
 ) 
 AND NUMBER IN (5,6)

Result

ID | TEST | CHR  |  NUMBER               
1  |  7   |  C   |    5        
2  |  7   |  T   |    6     
4  |  8   |  T   |    5  
6  |  9   |  G   |    5  
8  |  10  |  A   |    5  
11 |  14  |  G   |    5

edited Oct 14, 2014 at 9:22

Ullas

11.6k5 gold badges36 silver badges52 bronze badges

answered Oct 14, 2014 at 8:58

Naved Munshi

5071 gold badge5 silver badges17 bronze badges

1 Comment

user977828 Over a year ago

Thank you, but how would it possible to add to the above query to retrieve dynamically only rows with TEST column 7, because it has has 5 and 6 in the number column and no x?

hashbrown · Accepted Answer · 2014-10-15 13:23:04Z

0

Is this what you need?

[EDIT]

select o.*
FROM
test_data o,
(SELECT o.TEST
FROM test_data o
WHERE o.NUMBER = 5) five,
(SELECT o.TEST
FROM test_data o
WHERE o.NUMBER = 6) six
WHERE five.TEST = six.TEST
AND o.TEST = five.TEST
AND o.TEST NOT IN (SELECT distinct TEST FROM test_data WHERE chr = 'X')

The result is,

ID | TEST | CHR | NUMBER

1   7      C      5 
2   7      T      6

edited Oct 15, 2014 at 13:23

answered Oct 14, 2014 at 7:20

hashbrown

3,5461 gold badge23 silver badges37 bronze badges

2 Comments

user977828 Over a year ago

almost, it should only be test column with 7, because test column 7 have 5 and 6 in the number column and not X.

hashbrown Over a year ago

edited the query... hopefully it matches your expected result... looks ugly though

Collectives™ on Stack Overflow

Selecting data from multiple rows

4 Answers 4

Comments

Query

Fiddle Demo

5 Comments

Query

Result

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Query

5 Comments

Query

Result

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related