combining queries

[esport]

Hi Guys,

I have a product line table that consists of id, product_id, size and price. I want to be able to group each item by size but at the same time display all the prices of the product_id in question in that particular size. i.e a product may have 1 size but many prices for that size.

 

For example:

 

PIDprice1price2

1$30$50

 

Is this possible to do in 1 query or do i need to run 2 queries.

 

Thanks

 

Daniel

[fenway]

What do price1 and price2 represent?

[esport]

Each product may have 2 prices, its actually for a bedding website. Price 1 represents the mattress only and the price 2 represents it with ensemble. And there are many sizes of each product. i.e. Single, Double, Queen etc.

 

So I have a product line with all sizes for each product and if they are ensemble or not.

So the actual table should be more like:

 

product_id, size, price, ensemble.

 

However I want to group each product in size but since threre are 2 prices in a group, I was wondering how to do this in one query. I have managed to accomplish this, but running a second query to find out the ensemble price. I was hoping to do this in 1.

 

Thanks

 

Daniel

[mezise]

Please be precise what kind of data you have, e.g. this:

product_id  size     price  ensemble
----------  ------  ------  --------
         1  Single      10         0
         1  Double       0        20
         2  Double      20         0
         2  Queen        0        30

 

or e.g. this:

product_id  size     price  ensemble
----------  ------  ------  --------
         1  Single      10         20
         1  Double       15        25
         2  Double      20         30
         2  Queen        25        35

 

Or maybe something else?

[esport]

Yes but I have used the ensemble column as flag, either 1 or 0 to indicate the type of the item. So for example there would be 2 singles with the product ID of 1 and

product_id  size     price  ensemble
----------  ------    ------    --------
         1  Single       10          0
         1  Single       20          1
         1  Double      20          0
         1  Double      30          1

 

or e.g. this:

[mezise]

You should start with this example.

 

For this data to display products of every size with prices based on ensemble flag set or not set you may use query with LEFT JOINS:

SELECT
  p.product_id
  , p.size
  , p1.price price1
  , p2.price price2
FROM products p
LEFT JOIN products p1 ON p1.product_id = p.product_id
  AND p1.size = p.size
  AND p1.ensemble = 0
LEFT JOIN products p2 ON p2.product_id = p.product_id
  AND p2.size = p.size
  AND p2.ensemble = 1
GROUP BY
  p.size
ORDER BY 
  p.size
;

 

or query with SUBQUERIES:

 

SELECT
  product_id
  , size
  ,(SELECT
      p2.price
    FROM products p2
    WHERE
      p2.product_id = products.product_id
      AND p2.size = products.size
      AND p2.ensemble = 0
    LIMIT 1 
  ) AS price1
  ,(SELECT
      p2.price
    FROM products p2
    WHERE
      p2.product_id = products.product_id
      AND p2.size = products.size
      AND p2.ensemble = 1
    LIMIT 1 
  ) AS price2
FROM products
GROUP BY
  size
ORDER BY 
  size
;