Pivot table query assistance! 1

[quexal]

Well, I'm stumped on this one, the solution is probably staring me in the face, but I figgured I'd pop in here to ask quick-like.

Here's the table I'm working with:

Table Name: pprops

pp_id pl_id re_id
----- ----- -----
1 45 19
2 45 22
3 34 19
4 35 29

I'm using this as a pivot table, and would like to be able to pull out pl_id based on multiple constraints of re_id. For instance, using the sample data above, I would like have a SELECT query with the constraints of: re_id = 19 AND re_id = 22
With the result being: pl_id = 45

I thought I could do this using:
SELECT DISTINCT pl_id FROM pprops WHERE re_id = 19 AND re_id = 22;

Unfortunately, because re_id can only have 1 value in it I always get 0 results.

Will I have to use some form of subquery or self-join to get this to work?

Any and all help and or pointing in the right direction is greatly appreciated!

 

[TonyGroves]

Your query is selecting every record where the value of re_id is equal to 19 and equal to 22 at the same time; that is obviously impossible.

You would probably need something like the following:
[tt]
SELECT
pl_id,
CONCAT(',',GROUP_CONCAT(DISTINCT re_id),',') re_ids
FROM pprops
GROUP BY pl_id
HAVING re_ids LIKE '%,19,%' AND re_ids LIKE '%,22,%'
[/tt]

 

[quexal]

Hey Tony!
Thanks for the post... I wound up arriving at a solution that's pretty close to what you had there... here's what I'm now using!

SELECT pl_id, group_concat(re_id) AS re_ids
FROM pprops
GROUP BY pl_id
HAVING find_in_set(19, re_ids)
AND find_in_set(22, re_ids)

Working like a charm for any number of conditionals!

Thanks again,
Qxl

 

[guelphdad]

much easier is this (when you have more conditions of course. and I also believe find_in_set isn't standard sql):

SELECT pl_id, group_concat(re_id) AS re_ids
FROM pprops
where re_ids in (19,22)
GROUP BY pl_id
having count(*) =2

 

[r937]

guelphdad, your WHERE clause should be on re_id, not re_ids

re_ids can be dropped from the SELECT, too, because we know in advance that it's gonna be ='19,22', eh

;-)

r937.com | rudy.ca