CODEWITHSUNDEEP
mysqlsql
jbcedge
jbcedge

Reputation: 19515

SQL query price retrieval

I got 2 tables storing prices of the product.

The main table is Product which has all the product details.

The price of the products is being stored in two different tables with the following structures:

Now I want to merge those tables to get all the prices of the product.

Note: one product has price in either variant table or advance_price table. Not is both table.

I am looking for the result like this:

product.id, price

(this can be from variant table or advance_price table)

Any ideas will be appreciated.

Upvotes: 0

Views: 60

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270767

What you need is to COALESCE() the prices from the two tables, with appropriate joins:

SELECT
  Product.p_id,
  COLAESCE(variant.price, advance_price.price) AS price
FROM
  Product
  LEFT JOIN variant ON Product.p_id = variant.p_id
  LEFT JOIN advance_price ON Product.p_id = advance_price.p_id

COALESCE() will take the first non-null value in its arguments. LEFT JOIN is used in case no record exists in the variant or advance_price tables for a given p_id.

Upvotes: 3

Related Questions