0

I created a view, the following image is the query of it: 

select * from order_extended

order_extended

Is there a way to merge the products_id that are the same? something like this:

+-----+----------+----------------+---------+------+------+
| pid | quantity | product_name   | Walmart | Sams | Ley  |
+-----+----------+----------------+---------+------+------+
|   1 |   1.00   | ACEITE CAPULLO | 27.50   | 24.33| 29.90|
|   3 |   2.00   | ACEITE DE AJONJ| 40.00   | null | null | 
|   5 |   2.00   | ACEITE DE COCO | 114.00  | null | null | 
+-----+----------+----------------+---------+------+------+

This is the view definition:

create view order_extended as (
SELECT ol.products_id, ol.quantity, p.product_name, p.brand, 
case when s.name = "WALMART" then sp.purchase_price end as Walmart,
case when s.name = "SAMS" then sp.purchase_price end as Sams,
case when s.name = "LEY" then sp.purchase_price end as Ley,

FROM order_lists ol 
inner join products p on ol.products_id = p.id
inner join supplier_products sp on p.id = sp.products_id
inner join suppliers s on sp.supplier_id = s.id
);
altexo
  • 201
  • 3
  • 15
  • How do you want to merge rows that have multiple entries in the Walmart column for a particular products_id? Do you want to improve your view or write a new query? To improve your view, we need the data and query for that. – NetMage Feb 09 '18 at 22:28
  • Show the definition of your view. I suspect it could be easily changed to achieve the desired results using a group by. – Sloan Thrasher Feb 09 '18 at 22:35
  • I was trying to query the view to merge the rows and get the table that I drew, I updated the post with the definition of the view. – altexo Feb 09 '18 at 22:40

0 Answers0