mysql - 从一个表中选择多个列，从另一个表中获取相应的详细信息并将其插入到另一个表中

Question

主表:包含所有详细信息，例如proper_ID和proper_Name以及其他字段。

父表:有母亲和父亲的唯一ID，不存在proper_ID和proper_Name

子表:需要按照与父表相同的顺序插入proper_ID和proper_Name，并避免重复条目。

Master_Table
---------------------------------------------------------------
Proper_ID | Proper_Name | Proper_Address | Proper_Phone |Proper_Zipcode
----------------------------------------------------------------
ABC_235 | Pansy Montgomery | 427 Preston Court| 1234| 5679
KWH_631 | Price Maxwell | 164 Conduit Boulevard| 8782| 7893
DEA_124 | Howard Kelly | 314 Agate Court| 3234| 1529
FAE_832 | Best Mcpherson | 325 Dorchester Road| 1582| 1861


Parent_Table
---------------------------------------------------
M_ID | F_ID | Picture_URL | Age_Group | Email
---------------------------------------------------
235| 832 | http://placehold.it/32x32| 45 | espinozastrickland@accruex.com
631| 124 | http://placehold.it/32x32| 50 | roycooke@concility.com

Output Expected:
Child_Table
---------------------------------------------------
Mother_ID | Mother_Name | Father_ID | Father_Name
---------------------------------------------------
ABC_235 | Pansy Montgomery | FAE_832 | Best Mcpherson
KWH_631 | Price Maxwell | DEA_124| Howard Kelly 


select mt.proper_id, mt.proper_name from master_table mt, parent_table pt
where mt.proper_id in (pt.m_id, pt.f_id)

proper_id | proper_name
-------------------------
ABC_235 | Pansy Montgomery
FAE_832 | Best Mcpherson
KWH_631 | Price Maxwell  
DEA_124| Howard Kelly 

Answer 1

您可以创建Child_Table与 CREATE ... SELECT 陈述。您需要JOIN Parent_Table到Master_Table两次，一次获取母亲的详细信息，一次获取父亲的详细信息。请注意，您应该使用 ANSI 连接语法，逗号连接已被取代很长一段时间。

CREATE TABLE Child_Table AS
SELECT m1.Proper_ID AS Mother_ID,
       m1.Proper_Name AS Mother_Name,
       m2.Proper_ID AS Father_ID,
       m2.Proper_Name AS Father_Name
FROM Parent_Table p
JOIN Master_Table m1 ON SUBSTRING_INDEX(m1.Proper_ID, '_', -1) = p.M_ID
JOIN Master_Table m2 ON SUBSTRING_INDEX(m2.Proper_ID, '_', -1) = p.F_ID

然后就可以了

 SELECT *
 FROM Child_Table

输出:

Mother_ID   Mother_Name         Father_ID   Father_Name
KWH_631     Price Maxwell       DEA_124     Howard Kelly
ABC_235     Pansy Montgomery    FAE_832     Best Mcpherson

Demo on SQLFiddle

如果您只想为给定的 M_ID 对生成一行和F_ID值，您可以放置​​ UNIQUE索引 Mother_ID, Father_ID在Child_Table在 CREATE语句，然后要么 IGNORE重复或REPLACE他们:

CREATE TABLE Child_Table (UNIQUE(Mother_ID, Father_ID)) IGNORE
... -- as above

Demo on SQLFiddle