CODEWITHSUNDEEP
sql
Leigh
Leigh

Reputation: 1533

SQL Product Combinations

I have a SQL database that looks like this:enter image description here

With this database...

If I have a product, say a cotton shirt and this product is available in three options, say Size, Colour and Long or Short sleeves.

From my CMS I say the product is available in the following:
Large - White - Long Sleeves
Large - Green - Long Sleeves
Large - Green - Short Sleeves
Small - White - Long Sleeves

So on the front end of my site (in Javascript) I can say when Large is checked, enable White & Green check boxes. But when Small is checked, I need to only enable White check box etc.

The ideal results would look like this:
dependencies['Large'] = [ White, Green ]
dependencies['Large, White'] = [ Long Sleeves ]
dependencies['Large, Green'] = [ Long Sleeves, Short Sleeves ]
dependencies['Small'] = [ White ]
dependencies['Small, White'] = [ Long Sleeves ]

What might the SQL look like to achieve this?

Very grateful of any help
Thank you

Upvotes: 1

Views: 471

Answers (1)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171511

Rather than do a query each time an option is selected, you could use Javascript to modify the available selections. So your inital query would just return all data for a product in an array, XML, or JSON, and then you could filter it client-side as needed.

select * --replace the * with the fields you actually want
from Product p
left outer join Option o on p.Id = o.ProductId
left outer join OptionValue ov on o.id = ov.OptionId
left outer join CombinationDetail cd on ov.Id = cd.OptionValueId
left outer join Combination c on cd.CombinationId = c.Id

Upvotes: 1

Related Questions