product variant price view table query

Question

I have question need to query from product table and have many variants and prices, how can I show like below

product. variants.  price
==========================
Coke  -> Original -> 10$
      -> Zero     -> 8$
==========================
Pepsi -> Can      -> 9$
      -> Bottle.  -> 10$
==========================

if I have 3 tables : products , variants , product_variant_tranx

any one can help me ?

Thanks

Answer 1

It's a little be late. But this type of question is common for newcomers. I put here my answer for the newcomers who are coming here to search exact type of problems solution.

Every product may generate a unique id like p1,p2,... Under the products, you enter the variants like variant one, variant two,.. with a unique id(generate when entry, may be visible in the user interface or hidden for working backend) like p1v1,p1v2, with pricing like p1v1 10$,p1v2 8$ etc.

Let's see the tables

Table1. products_table

product_name	product_id
Coke	p1
Pepsi	p2

Table2. variants_table

product_id	variant_name	variant_id	variant_price
p1	Original	p1v1	10$
p1	Zero	p1v2	8$
p2	Can	p2v1	9$
p2	Bottle	p2v2	10$

Call to the user interface

Query1. SELECT product_name,product_id FROM products_table

Then call

Query2. SELECT variant_name,variant_id,variant_price FROM products_table WHERE product_id='getting product id from Query1'

I show the basic logic for newcomers. If having a large number of products and variants then inner looping can jam your server. For this, you may use the same database structures but can use advanced SQL queries.

Relative sources which may help:
https://dev.mysql.com/doc/ (For learning advanced SQL queries)
https://cashflowinventory.com/blog/cash-flow-inventory-tutorial-products-management/ (Get concepts from a likely application's product management interface)