Tricky one, mysql pivot table or equivalent help!

[chilli]

Ok so I know it's a car forum, but some of you might be able to help, I hope!

 

I need to run a query that has many individual parts, but they need to be treated as a whole set on multiple rows.

 

Greately simplified, something like this:

 

Query is something like 'get me all rows as a whole group that match the clause conditions'

 

where there are clauses like this:

 

((q = 3) and (v 6))

 

from a table something like:

 

id g q v

1 1 1 3

2 1 2 2

3 1 3 4

4 1 5 8

5 1 6 1

1 2 1 3

2 2 2 2

3 2 3 6

4 2 5 8

5 2 6 1

...

 

which should work like this.

 

for group g = 1, q = 3, v 6 is valid, so return me all rows for g = 1

 

for group g = 2, q = 3, v 6 is bot valid, so don't return me all rows for g = 2

 

and so on...

 

so the result would be in this case

 

id g q v

1 1 1 3

2 1 2 2

3 1 3 4

4 1 5 8

5 1 6 1

 

as only group 1's rows match _all_ the clause conditions as a complete set.

 

the only way I can think to do this is to create a pivot table and put all the q values as columns and then I can run a normal query that tests q3>x and q5

 

so that leads me to my question

 

anyone know how to do a generic pivot in mysql (5.x) or know another way to solve this (maybe using stored procs)?

 

any help greatly appreciated, I'll carry on muddling through in the mean time hoping for a brainwave!

[Guest supraimpose]

Hey there,

 

I'm no DBA, as I mostly deal with Java, but I thought I'd have a go. I think you may be able to do this as an SQL statement, and not have to resort to pivot tables or other trickery. Here's what I came up with:

select * from table t1 where \
t1.g in (select DISTINCT g from table \
MINUS \
select DISTINCT g from table t2 where \
(t2.q = 3 and \
t2.v >= 5) or \
(t2.q = 5 and \
t2.v 

Basically, you compile a list of all the groups that DONT match your criteria, subtract that from the full list of groups, then use the resulting list of groups to get all the rows in those groups.

 

The key is that the main work is done by finding the rows that DONT match the ones you want. This means where you want (q = 3 and v = 5).

 

This is all under the assumption that I understand what you are trying to do... which I'm not 100% sure I do And again, I'm no DBA, and what little database experience I have has been with DB2, so this may all be utter rubbish