1

sorry for the weird question but I didn't know how to exactly ask this. Or if there are similar questions to look at, please let me know.

I have the following table in my database. Basically it stores the prices of an item at different stores. Every store will always have the same items, meaning if store_id 3, 4,... n values are later added, they will have the same item_ids

+----+---------+----------+---------+
| id | item_id | store_id |  price  |
+----+---------+----------+---------+
|  1 |       1 |        1 |    74.99|
|  2 |       2 |        1 |    25.99|
|  3 |       3 |        1 |    89.99|
|  4 |       1 |        2 |    69.99|
|  5 |       2 |        2 |    39.99|
|  6 |       3 |        2 |    95.99|
+----+---------+----------+---------+

My sql knowledge is pretty basic, but I was wondering if there is a way to get this result.

I need to add every store_id value as a column and then get the price value for the item_id at every store available and put that in a single row. Since there could be more values for store_id, every different value

+---------+-----------+-----------+
| item_id | price for | price for |
|         | store_id 1| store_id 2|
+---------+-----------+-----------+
|       1 |     74.99 |      69.99|
|       2 |     25.99 |      39.99|
|       3 |     89.99 |      95.99|
+---------+-----------+-----------+

Thanks.

raptorandy
  • 225
  • 5
  • 21

1 Answers1

0

you could use a self join eg:

  select a.item_id, a.price as price_store_1, b.price  as price_store_2
  from my_table a
  inner join my_table b on a.item_id = b.item_1 and b.store_id = 2
  where a.store_id = 1
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107