Select columns thats greater than?

Question

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?

This isn't really how relational databases are supposed to be used. If you have more than one row, you'd have variable columns as some would and wouldn't be included per row based on the contents. — Jeff Watkins, Sep 03 '15 at 08:14
If the date is not unique and you will want to ask later for more than one ROW then you cannot do this in SQL. If you want only single row and the date is unique, then just get the resukt and edit it later... — Balinti, Sep 03 '15 at 08:30
the date is uniqe i have one row for every day like '2015-09-03' and so on, i can accomplish this later with jquery but i was wondering if i could filter this in my question so i dont need to if/else me throw all columns — Locos, Sep 03 '15 at 08:32
A SELECT statement always returns the same set of columns, independent of the current table data. So you can not write a SELECT that for sometimes returns 7 columns, and sometimes 6, or 8 or any other number of columns. — jarlh, Sep 03 '15 at 08:43
Okej @jarlh thank you, then i just use jquery or php to filter — Locos, Sep 03 '15 at 08:46

Jeff Watkins · Accepted Answer · 2015-09-03T08:36:28.590

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

score -2 · Answer 2 · answered Sep 03 '15 at 08:17

-2

Try this

SELECT * FROM my_table WHERE id >= 4 AND foo1 >=4 AND foo2 >=4 AND date = '2015-09-03'

Fill the rest of the foos, Avoid using IS and comparison operators together.

answered Sep 03 '15 at 08:17

Sadiq Jaffer

72
9

so the output will have only columns which you run in this query? – Jai Sep 03 '15 at 08:18
@Jai As long as he have fixed structure then, Yes. safe way. – Sadiq Jaffer Sep 03 '15 at 08:19
Nope, it always selects ALL columns (SELECT * tends to do this!) and only the rows where the ID is > 4 (for what reason???) and foo1 is >4. Which makes precious little sense! – Jeff Watkins Sep 03 '15 at 08:29

Select columns thats greater than?

2 Answers2