[SOLVED] Query to obtain set of boolean parameters

[svivian]

I have a table of the form:

p_id / param

where 'p_id' is a foreign key to an id in another table, and 'param' specifies a parameter applied to that object in the other table.

 

So I might have some data like:

1 / param1

1 / param3

2 / param2

2 / param3

Only the parameters that are "true" are set in the table.

 

What I want to do is get a table like this:

p_id / param1 / param2 / param3
1    / 1      / 0      / 1
2    / 0      / 1      / 1

 

So a set of parameters (that I choose) are used as the fields, and I have a boolean for whether the parameter is on that object or not. Hopefully that makes sense!

 

How would I do this? I've tried a few join queries and such but can't get my head round how to select values from different rows. Grouping doesn't seem to work (though I could probably just get a comma-separated list using the GROUP_CONCAT function).

[rhodesa]

i assumed your table you are joining with is called PRODUCTS:

 

SELECT p.id, 
  CASE p1.param
    WHEN 'param1' THEN 1
    ELSE 0
  END as param1,
  CASE p2.param
    WHEN 'param2' THEN 1
    ELSE 0
  END as param2,
  CASE p3.param
    WHEN 'param3' THEN 1
    ELSE 0
  END as param3
FROM products p
  LEFT JOIN params p1 ON p.id = p1.p_id AND p1.param = 'param1' 
  LEFT JOIN params p2 ON p.id = p2.p_id AND p2.param = 'param2' 
  LEFT JOIN params p3 ON p.id = p3.p_id AND p3.param = 'param3'

[svivian]

Thanks! That's working a treat.

 

BTW I changed the CASE statements in order to shorten the query and make it easier to read. This brings back NULL instead of 0 for "false" which is fine for me. So instead of...

CASE p1.param
    WHEN 'param1' THEN 1
    ELSE 0
  END as param1

I have...

p1.param='param1' AS param1

[rhodesa]

Thanks! That's working a treat.

 

BTW I changed the CASE statements in order to shorten the query and make it easier to read. This brings back NULL instead of 0 for "false" which is fine for me. So instead of...

CASE p1.param
    WHEN 'param1' THEN 1
    ELSE 0
  END as param1

I have...

p1.param='param1' AS param1

 

good call. i was certain there was an easier way then the case statement...just couldn't think of it