在没有公用键的情况下联接多个表 [英] Joining multiple tables without common key

问题描述

您好，我目前有3个表格，如下所示.表之间没有通用键

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

表1->链接表

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

表2->材料表

ID Name          
1  plastic
2  wool
3  leather

表3->颜色表

ID Color
1  Yellow
2  green
3  orange

我希望得到以下结果集

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

我想编写一条SQL语句将表连接在一起.

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.

我现在确实需要此解决方案.一直试图解决这个问题超过5个小时.预先感谢您的帮助.

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

推荐答案

一种可能的方法:

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小提琴

说明:必须建立查询，以便当在相应字段中给出'*'时，材料"和颜色"表完全连接(交叉连接)，或者通过这些字段的相等性连接.这正是我们使用'ON l.someField = '*' OR l.someField = joined.someField'子句所得到的.

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.

这篇关于在没有公用键的情况下联接多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！