Joining multiple tables without common key

Question

Hi I currently have 3 tables as listed below. There is no common keys between the tables

Table 1->linkage_Table

ID Item         Material       Color
1  keypouch     *              yellow
2  wallet       plastic        *
3  card-holder  leather        gold

Table 2->Material_Table

ID Name          
1  plastic
2  wool
3  leather

Table 3->Color_Table

ID Color
1  Yellow
2  green
3  orange

I would wish to get the following result set

Item         Material    Color

keypouch     plastic     yellow
keypouch     wool        yellow
keypouch     leather     yellow
wallet       plastic     yellow
wallet       plastic     green
wallet       plastic     orange
card-holder  leather     gold

I would like to write an SQL statement to join the tables together.

Having * in linkage table would mean that we would retrieve all values from either Material or Color table.

I am really in need of this solution now. Been trying to solve this for more than 5hours. Thanks in advance for any help.

Answer 1

One possible approach:

SELECT l.Item, m.name, c.Color
      FROM linkage_Table AS l
INNER JOIN Material_Table AS m
        ON l.Material = '*'
           OR l.Material = m.name
INNER JOIN Color_Table AS c
        ON l.Color = '*'
           OR l.Color = c.Color

SQL Fiddle

Explanation: the query has to be built so that 'material' and 'color' tables are joined either completely (cross-join), when '*' is given in the corresponding field, or by equality of these fields. And that's exactly what we got by using 'ON l.someField = '*' OR l.someField = joined.someField' clause.

Answer 2

With example:

CREATE TABLE linkage_table (
  id INT,
  item VARCHAR(40),
  material VARCHAR(40),
  color VARCHAR(40)
);

CREATE TABLE material_table (
  id INT,
  name VARCHAR(40)
);

CREATE TABLE color_table (
  id INT,
  color VARCHAR(40)
);

INSERT INTO linkage_table VALUES (1, 'keypouch', '*', 'yellow');
INSERT INTO linkage_table VALUES (2, 'wallet', 'plastic', '*');
INSERT INTO linkage_table VALUES (3, 'card-holder', 'leather', 'gold');

INSERT INTO material_table VALUES (1, 'plastic');
INSERT INTO material_table VALUES (2, 'wool');
INSERT INTO material_table VALUES (3, 'leather');

INSERT INTO color_table VALUES (1, 'yellow');
INSERT INTO color_table VALUES (2, 'green');
INSERT INTO color_table VALUES (3, 'orange');

SELECT l.item AS Item, m.name AS Material, IFNULL(c.Color, l.color)
  FROM linkage_table l
    LEFT JOIN material_table m ON (l.material = m.name OR l.material = '*')
    LEFT JOIN color_table c ON (l.color = c.color OR l.color = '*')
;

Returns exactly what you wanted. Not sure if your sample data is lacking 'gold' colour on purpose?

Check at SQLFiddle: http://sqlfiddle.com/#!2/d9d3d/4