CODEWITHSUNDEEP
mysqlsql
user1405177
user1405177

Reputation: 477

How to select all rows from a table given conditions of rows?

I have two tables:

    users
| id |  name  |
|----|--------|
| 0  |  Bob   |
| 1  |  Ted   |

        parameters
| id | parameter | value  |
|----|-----------|--------|
| 0  |     a     |   5    |
| 0  |     b     |   9    |
| 0  |     c     |   0    |
| 1  |     a     |   27   |
| 1  |     b     |   0    |

*parameters.id references users.id

Unfortunately, the only way to normalize values was in the form provided. The parameters are not consistent for all users, and cannot be the columns.

I must always select all parameters and their values for each user, however I may need to query parameters for certain values (while still grabbing ALL rows for the user.)

For example, I may want to get all parameters where the parameter a has a value greater than 10. This would return:

| id | name  |  parameters |  values  |
|----|-------|-------------|----------|
| 1  |  Ted  |      a,b    |   27,0   |

I can get all of the data from the parameters table, but I am at a loss how I can conditionally select the data:

SELECT a.*, b.params, b.values
FROM users AS a
JOIN (
    SELECT id, GROUP_CONCAT(parameters) AS params, GROUP_CONCAT(value) AS values
    FROM parameters
    GROUP BY id
) b
ON a.id=b.id

Is there a way to achieve this, or is there a better database design?

(If anyone has a better title for this question... Feel free)

Upvotes: 0

Views: 78

Answers (1)

Brian DeMilia
Brian DeMilia

Reputation: 13233

This would get your expected output of one row with that example data, under the assumption you want to ignore users without a parameter a of > 10 (entirely):

Fiddle Test: http://sqlfiddle.com/#!2/d70d5/5/0

select p.id,
       u.name,
       group_concat(p.parameter) as params,
       group_concat(p.value) as vals
  from parameters p
  join users u
    on p.id = u.id
  join (select id
          from parameters
         where parameter = 'a'
           and value > 10) v
    on p.id = v.id
 group by p.id, u.name

However if you want parameter a to only come back if above 10, but still show users' other parameters regardless, you would want:

Fiddle Test: http://sqlfiddle.com/#!2/d70d5/4/0

select p.id,
       u.name,
       group_concat(p.parameter) as params,
       group_concat(p.value) as vals
  from parameters p
  join users u
    on p.id = u.id
 where (p.value > 10 and p.parameter = 'a')
    or p.parameter <> 'a'
 group by p.id, u.name

(notice how Bob's value of 5 for parameter a is not returned because 5 < 10 whereas for Ted's value of 27 for parameter a is returned because 27 > 10 and values for b and c are returned regardless)

Upvotes: 1

Related Questions