[SOLVED] Select columns thats greater than?

Issue

I have a table(mysql) and in my query I would like to get only the columns that are greater or equal to 4, but I have no clue how to do that.

SELECT * FROM my_table WHERE * (all foo columns foo1-foo7) >= 4 AND date = '2015-09-03'

My table looks like:

id | foo1 | foo2 | foo3 | foo4 | foo5 | foo6 | foo 7 | date
-----------------------------------------------------
1  |   5  |  10  |  8   |  0   |   2  |  4   |  5    | 2015-09-03
2  |   7  |  18  |  0   |  1   |   0  |  5   |  7    | 2015-09-04

so my result should be:

id | foo1 | foo2 | foo3 | foo6 | foo 7 | date
-----------------------------------------------------
1  |   5  |  10  |  8   |  4   |  5    | 2015-09-03

Is this possible?

Solution

The more appropriate answer (for an RDBMS) is to use two tables with a foreign key relationship and constraint.

MASTER
ID                DATE
1                 2015-09-03

DETAIL
ID       MASTER_ID    MYNAME  MYVALUE
1        1            tom     5
2        1            bill    10
3        1            kev     8
4        1            bob     0
5        1            other   2
6        1            bleh    4
7        1            snarf   5

then

SELECT m.id, m.date, d.myvalue FROM master m 
INNER JOIN detail d ON m.id = d.master_id 
WHERE m.date = '2015-09-03' AND d.myvalue > 4

this gives you multiple rows, but then you can use your RDBMS’s PIVOT function to turn this into something else if you wish.

e.g.

SELECT m.id, m.date, d.myvalue FROM master m 
INNER JOIN detail d ON m.id = d.master_id 
WHERE m.date = '2015-09-03' AND d.myvalue > 4
PIVOT myvalue FOR myname

(sql server syntax) you’d end up with

ID    date        tom    bill    kev    snarf
1     2015-09-03  5      10      8      5

Answered By – Jeff Watkins

Answer Checked By – Pedro (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.