Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

SQL JOIN Help?

Options
  • 15-09-2009 6:28pm
    #1
    smcelhinney
    Registered Users Posts: 1,127 ✭✭✭


    Hey guys

    Struggling with the concept of a join query for a specific resultset Im trying to get. I have 3 tables:
    • Products - id, product_name
    • Pricing Categories - id, category_name
    • Pricing - product_id, category_id, price

    Not sure if the relationship is apparent, but basically I want to output a table like this
    +-------------------------------+------------+------------+------------+------------+
| Product name                  | p_cat_1    | p_cat_2    | p_cat_3    | p_cat_4    |
+-------------------------------+------------+------------+------------+------------+
| Widget 1                      | 0.00       | 0.00       | 0.00       | 0.00       |
+-------------------------------+------------+------------+------------+------------+
| Widget 2                      | 0.00       | 0.00       | 0.00       | 0.00       |
+-------------------------------+------------+------------+------------+------------+
| Widget 3                      | 0.00       | 0.00       | 0.00       | 0.00       |
+-------------------------------+------------+------------+------------+------------+
| Widget 4                      | 0.00       | 0.00       | 0.00       | 0.00       |
+-------------------------------+------------+------------+------------+------------+

    where
    • there's an undetermined number of pricing categories (I know if there was 3 or 4 I could just subselect).
    • the price will display for each pricing category (lookup on pricing table, using current product id and pricing category id)

    Does anyone know the syntax for this? No idea where to start even.

    Thanks!


Comments

  • Options
    #2
    showry
    Registered Users Posts: 1,453 ✭✭✭showry


    You can do it in 2 stages, or use a stored procedure with dynamic SQL.

    First, create the case statements for each column:
    select group_concat(concat('sum(case when cat_id=',id,' then price else 0 end) "',category,'"')) from pric_cat_a

    This will return the following string:
    sum(case when cat_id=1 then price else 0 end) "cat1",sum(case when cat_id=2 then price else 0 end) "cat2",sum(case when cat_id=3 then price else 0 end) "cat3"

    You can then use this string in your main query:
    mysql> select prod_id, sum(case when cat_id=1 then price else 0 end) "cat1",sum(case when cat_id=2 then price else 0 end) "cat2",sum(case when cat_id=3 then price else 0 end) "cat3" from pricing_a group by prod_id;
    +
    +
    +
    +
    +
    | prod_id | cat1 | cat2 | cat3 |
    +
    +
    +
    +
    +
    | 1 | 10 | 20 | 30 |
    | 2 | 15 | 25 | 35 |
    | 3 | 12 | 22 | 32 |
    +
    +
    +
    +
    +
    3 rows in set (0.00 sec)


  • Options
    #3
    smcelhinney
    Registered Users Posts: 1,127 ✭✭✭smcelhinney


    Hmm,

    I see what you've done. Its nice if I then pass the result of the group_concat back to my server side application and run it again, but Id like to be able to wrap it all in one select statement. The end result would be a temporary table with 1 column for the product id, and n columns for the pricing categories. I can extrapolate the metadata such as column headers programmatically, so I dont need to worry about that server side.

    I'll investigate this more, I appreciate your help, but if anyone has any other solution using a single select statement, I would bite your hand off for it.

    Thanks showry.


  • Options
    #4
    qwytre
    Closed Accounts Posts: 409 ✭✭qwytre


    something like this...

    select product_name,
    sum(case when cat_id=1 then price else 0 end) "cat1",
    sum(case when cat_id=2 then price else 0 end) "cat2",
    sum(case when cat_id=3 then price else 0 end) "cat3"
    from
    (select product_name,cat_id,sum(price)price
    from Pricing_Categories pc
    inner join Pricing p on (p.id=pc.category_id)
    group by product_name,cat_id
    )result
    group by product_name


  • Options
    #5
    showry
    Registered Users Posts: 1,453 ✭✭✭showry


    smcelhinney wrote: »
    Hmm,

    I see what you've done. Its nice if I then pass the result of the group_concat back to my server side application and run it again, but Id like to be able to wrap it all in one select statement. The end result would be a temporary table with 1 column for the product id, and n columns for the pricing categories. I can extrapolate the metadata such as column headers programmatically, so I dont need to worry about that server side.

    I'll investigate this more, I appreciate your help, but if anyone has any other solution using a single select statement, I would bite your hand off for it.

    Thanks showry.

    You could use a stored procedure 
    create procedure testproc2()
begin
declare casex varchar(255);
select group_concat(concat('sum(case when cat_id=',id,' then price else 0 end) "',category,'"')) into casex from pric_cat_a;
set @s = concat('select prod_id, ',casex,' from pricing_a group by prod_id');
prepare stmt1 from @s;
execute stmt1;
end;

mysql> call testproc2;
+---------+------+------+------+
| prod_id | cat1 | cat2 | cat3 |
+---------+------+------+------+
|       1 |   10 |   20 |   30 | 
|       2 |   15 |   25 |   35 | 
|       3 |   12 |   22 |   32 | 
+---------+------+------+------+
3 rows in set (0.08 sec)

Query OK, 0 rows affected (0.08 sec)


Advertisement