Archive for September 8th, 2009
From Stack Overflow:
I have a database that stores productsavailable on the marketand productsstill in developmentin two separate tables (
A third table (
substance) contains all substances a product can made of.
Other two tables (
dev_product_comp) mantains product compositions.
I want to select products still in development that are made of the same ingredients of marketed products.
This means that we need to return every
dev_product that has at least one
market_product with complete substance match.
In the following example:
we should return development product 1, since it’s made of apples and bananas, and there is at least one product already on market (namely, market product 2) that is made of exactly same things and nothing else.
We should not return development product 3, because these is no complete match with market product 3, since is daikons are used to make the latter but not the former.
In one of the previous articles I wrote about a similar task in SQL Server:
However, this used
FULL OUTER JOIN which MySQL lacks.
Let’s see how to implement this task in MySQL: