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)