CODEWITHSUNDEEP
mysqlsql
Louis
Louis

Reputation: 65

Joining multiple tables without common key

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.

Upvotes: 3

Views: 4697

Answers (3)

raina77ow
raina77ow

Reputation: 106385

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.

Upvotes: 3

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

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

Upvotes: 1

Richard Newman
Richard Newman

Reputation: 630

Rather than use * in a table since a JOIN will not pick up on multiple entries in a target table based on a use of * in a source table you need to have an entry in the database for each record.

ID Item Material Color 1 keypouch plastic yellow 4 keypouch wool yellow 5 keypouch leather yellow

And again a similar thing for the colour. Additionally since you're going to be joining data based on common columns it is usually better (and faster) to use the primary key of the target table as a the column in the source table from which to join. This allows the joins to use the indexes and perform faster.

Upvotes: 0

Related Questions