CODEWITHSUNDEEP
mysqldatabaseschema
caiquearaujo
caiquearaujo

Reputation: 417

How to load default values using inner join?

I have a really simple product inventory. Each products has one or more SKUs.

In the database schema, Product is the parent model while ProductSKUs is the child model. A Product has the master and default attributes, and ProductSKUs can replace this attributes. Look:

--PRODUCT
id
thumbnail_id
gallery
name
slug
description
sku
price
weight
length
height
depth

--PRODUCTSKUS
id
parent_id
thumbnail_id
gallery
name
slug
description
sku
price
weight
length
height
depth

What I want to achieve is replace the Product fields only when ProductSKUs is empty. For example:

A) Product has the thumbnail_id = 10 and ProductSKUs thumbnail_id = null, then when SELECT... ProductSKUs will return thumbnail_id = 10.

B) Product has the price = 109.99 and ProductSKUs price= null, then when SELECT... ProductSKUs will return price = 109.99.

C) Product has the price = 109.99 and ProductSKUs price= 99.99, then when SELECT... ProductSKUs will return price = 99.99.

This simple schema is the best way to achieve this? What SQL INSTRUCTION would be more efficent to get all ProductSKUs including the parent default values?

Upvotes: 0

Views: 23

Answers (1)

ysth
ysth

Reputation: 98398

Use coalesce on each field you want to default:

select
    coalesce(productskus.thumbnail_id,product.thumbnail_id) as thumbnail_id,
    ...
from product
join productskus on parent_id=product.id

Upvotes: 1

Related Questions