sql select question

I have 4 tables;

• articles
• machines
• features
• required features

the problem is that articles can be produced on machines but only on the machines that have the features that are required by the articles. a product can require 0 or more features and a machine can have 0 or more features

I want to create a query that shows a complet overview of valid article-machine combinations. the question is: How can I do that?

Below is an example data set for MySQL. it should result in the following query result:

"car";"virtual machine"
"boat";"virtual machine"
"boat";"lean machine"

here's the dataset:

CREATE TABLE IF NOT EXISTS `articles` (
  `name` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `articles` (`name`) VALUES
('car'),
('boat');

CREATE TABLE IF NOT EXISTS `features` (
  `machine` text NOT NULL,
  `feature_name` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `features` (`machine`, `feature_name`) VALUES
('lean machine', 'punch'),
('virtual machine', 'punch'),
('virtual machine', 'drill');

CREATE TAB开发者_StackOverflow社区LE IF NOT EXISTS `machines` (
  `name` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `machines` (`name`) VALUES
('lean machine'),
('virtual machine');

CREATE TABLE IF NOT EXISTS `required_features` (
  `article` text NOT NULL,
  `feature` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `required_features` (`article`, `feature`) VALUES
('car', 'drill'),
('boat', 'punch');

It's just a couple of joins.

SELECT DISTINCT a.article, f.machine
FROM articles AS a
INNER JOIN required_features AS rf
    ON rf.article = a.article
INNER JOIN features AS f
    on f.feature_name = rf.feature

This will give you all valid combinations of article and machine as related through those tables. You could get away with one JOIN probably since the articles table is unneeded for this query - just select article from the required_features table and join to features.

If you want more info, you could change the last join to a LEFT JOIN, and a NULL value for f.machine would tell you that the article can't currently be produced.

With the data you provide, the solution is just a few joins - but I'm assuming that it's possible for an article to require more than one feature, and for a machine to have one of those features, but not all the features required.

In this case, you would need to also make sure that a machine that has a matching feature of an article also doesn't lack any other features required by the article. I don't think a left join would do the job because it would then return the machine for the matching feature, and the null for the non-matching feature... whereas it shouldn't return the machine at all in this case.

SELECT DISTINCT
    a.name,
    m.name
FROM
    articles a INNER JOIN
    required_features rf ON rf.article = a.name INNER JOIN
    features f ON f.feature_name = rf.feature INNER JOIN
    machines m 
        ON m.name = f.machine
        -- Make sure that there is no feature required that isn't provided by the machine.
        AND NOT EXISTS (
            SELECT 1
            FROM 
                machines m2 INNER JOIN
                features f2 ON f2.machine = m2.name LEFT JOIN
                required_features rf2 ON rf2.feature = f2.feature_name
            WHERE 
                m2.name = m.name AND
                rf2.feature IS NULL
                AND rf2.article = a.name
        )

select *
from articles ar
join required_features rf on rf.article=ar.name
join features f on f.feature_name=rf.feature
join machines m on m.name=f.machine