如何为SQL初学者处理多个查找表? [英] How to deal with multiple lookup tables for beginners of SQL?

问题描述

我很难把头缠在多个查找表上.我有一个中央表，其中每列中都包含ID以外的所有信息:

I'm having a hard time wrapping my head around multiple lookup tables. I have a central table with all the info but IDs in each column:

db_supply (Main supplier information data table)
 -----------------------------------------------
| Supplier_ID | Supply_Type_ID | Itm_ID | Stock |
|-----------------------------------------------|
| 1           | 1              | 33     | 3     |
|-----------------------------------------------|
| 2           | 2              | 28     | 1     |
 -----------------------------------------------

lookup_supplier
 -----------------------------
| Supplier_ID | Supplier_Name |
|-----------------------------|
| 1           | Walter        |
|-----------------------------|
| 2           | Jesse         |
 -----------------------------

lookup_supply_type
 -----------------------------------
| Supply_Type_ID | Supply_Type_Name |
|-----------------------------------|
| 1              | Import           |
|-----------------------------------|
| 2              | Delivery         |
 -----------------------------------

lookup_itm
 ------------------------
| Itm_ID | Itm_Name      |
|------------------------|
| 33     | Pickles       |
|------------------------|
| 28     | Burger        |
 ------------------------

A query for 'Burger' will output this table:
 -----------------------------------------------------
| Supplier_Name | Supply_Type_Name | Itm_Name | Stock |
|-----------------------------------------------------|
| Jesse         | Delivery         | Burger   | 1     |
 -----------------------------------------------------

我实际上将如何实现?到目前为止，我有:

How would I actually achieve this? So far I have:

SELECT * FROM db_supply WHERE Itm_ID = (SELECT Itm_ID FROM lookup_itm WHERE Itm_Name = 'Burger')

哪个会给我db_supply表的第二行，其ID尚未查找.我是否必须嵌套我的选择来查找多个表?这里不知道.

Which would give me the second row of the db_supply table with IDs not looked up yet. Do I have to nest my selects to look up multiple tables? No idea here.

推荐答案

SELECT  b.Supplier_Name,
        c.Supply_Type_Name,
        d.Itm_Name,
        a.Stock
FROM    db_supply a
        INNER JOIN lookup_supplier b
            ON a.Supplier_ID = b.Supplier_ID
        INNER JOIN lookup_supply_type c
            ON a.Supply_Type_ID = c.Supply_Type_ID
        INNER JOIN lookup_itm d
            ON a.Itm_ID = d.Itm_ID
WHERE   d.Itm_Name = 'Burger'

• SQLFiddle演示
• SQLFiddle Demo

要进一步获得有关联接的知识，请访问下面的链接:

To further gain more knowledge about joins, kindly visit the link below:

• SQL联接的可视表示

输出

╔═══════════════╦══════════════════╦══════════╦═══════╗
║ SUPPLIER_NAME ║ SUPPLY_TYPE_NAME ║ ITM_NAME ║ STOCK ║
╠═══════════════╬══════════════════╬══════════╬═══════╣
║ Jesse         ║ Delivery         ║ Burger   ║     1 ║
╚═══════════════╩══════════════════╩══════════╩═══════╝

这篇关于如何为SQL初学者处理多个查找表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！