CODEWITHSUNDEEP
mysqlsql
Elysium
Elysium

Reputation: 135

MySQL query to get required data from table

I have a table like this:

RS    Value    Measure    Feature
1     x1       a          5k 
1     x2       b          5k
1     x3       b          10k
1     x4       c          10k
2     y1       a          5k
2     y2       c          5k
2     y3       b          10k
2     y4       d          10k  
.     .        .          .
.     .        .          .
.     .        .          .

there are diff. values of RS(like 1,2,3,4,....)

now i want a query(taking only two values of RS, here 1,2) that returns the result as follows:

RS    Value    Measure    Feature
1     x1       a          5k
2     y1       a          5k
1     x3       b          10k
2     y3       b          10k
.     .        .          .
.     .        .          .

i.e, i want only those rows which have common Measure and Feature.

Upvotes: 0

Views: 79

Answers (2)

Majenko
Majenko

Reputation: 1840

For this you basically need to look at the data twice within the same query and compare the data to itself. There's many ways of doing it, but the first I could come up with is to just reference the table twice.

Given the table "bar" with this data:

mysql> select * from bar;
+------+-------+---------+---------+
| rs   | value | measure | feature |
+------+-------+---------+---------+
|    1 | x1    | a       | 5k      |
|    1 | x2    | b       | 5k      |
|    1 | x3    | b       | 10k     |
|    1 | x4    | c       | 10k     |
|    2 | y1    | a       | 5k      |
|    2 | y2    | c       | 5k      |
|    2 | y3    | b       | 10k     |
|    2 | y4    | d       | 10k     |
+------+-------+---------+---------+
8 rows in set (0.00 sec)

by my interpretation of your requirements (which may be wrong) you want to select all the lines that have at least two entries, where measure and feature match and the "rs" is one of two values.

The query I have come up with is as follows:

SELECT 
    bar.rs, bar.value, bar.measure, bar.feature 
FROM 
    bar, bar AS obar 
WHERE 
    ( bar.rs == 1 OR bar.rs == 2 )
  AND
    obar.measure = bar.measure 
  AND
    obar.feature = bar.feature
  AND
    obar.rs != bar.rs 
  ORDER BY
    measure, rs;

That is basically saying that you want all the rows from bar where there is a matching entry in obar (which is basically bar by another name), but where the rs column differs.

The result of running the query is:

+------+-------+---------+---------+
| rs   | value | measure | feature |
+------+-------+---------+---------+
|    1 | x1    | a       | 5k      |
|    2 | y1    | a       | 5k      |
|    1 | x3    | b       | 10k     |
|    2 | y3    | b       | 10k     |
+------+-------+---------+---------+
4 rows in set (0.00 sec)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can express this as a not exists query:

select t.*
from table t
where exists (select 1
              from table t2
              where t2.measure = t.measure and
                    t2.feature = t.feature and
                    t2.rs <> t.rs
             );

Upvotes: 1

Related Questions