PostgreSQL - Multiple select primary key from three tables and insert as foreign key into one table

Question

Edit: Apologize for blur and confused example, I'm going to edit the example to make the relations clearer.

I have a COMPANY_RAWDATA table as shown as follow:

 com_name  |  com_number | com_category | com_status 
-----------+-------------+--------------+-------------
Starbucks  | Star0121    | Drinks       | Active
KFC        | KFC1122     | Fast food    | Active
MCD        | MCD0000     | Fast food    | Active
MB         | MB2202      | Fast food    | Active
Chatime    | Pizza99     | Drinks       | Liquidation

The raw data are normalized into four tables which are:

(1) COMPANY_DETAIL with com_detail_id as PK

com_detail_id  | com_name  | com_number
---------------+-----------+------------
            1  | Starbucks | Star0121
            2  | KFC       | KFC1122
            3  | MCD       | MCD0000
            4  | MB        | MB2202
            5  | Chatime   | Pizza99

(2) COMPANY_CATEGORY table with com_category_id as PK

 com_category_id | com_category 
-----------------+--------------
               1 | Fast food    
               2 | Drinks       
               3 | Restaurant   

(3) COMPANY_STATUS table with com_status_id as PK

 com_status_id |    com_status   
---------------+-------------------
             1 | Active                    
             2 | Liquidation        
             3 | In Administration  

(4) COMPANY table with com_id as PK and other column possess FK constraints, they have relationship with each other.

com_id  |  com_detail_id | com_category_id | com_status_id 
--------+----------------+-----------------+---------------

All the primary key mentioned from (1) to (4) are declared with SERIAL.

The expected output should be:

   com_id  |  com_detail_id | com_category_id | com_status_id 
-----------+----------------+-----------------+---------------
        1  |              1 |               2 |             1
        2  |              2 |               1 |             1
        3  |              3 |               1 |             1
        4  |              4 |               1 |             1
        5  |              5 |               2 |             2

The three query to insert into specific columns written into a script are shown as below:

INSERT INTO company_rawdata (com_detail_id)
SELECT com_detail_id FROM company_detail AS detail, 
                          company_rawdata AS raw
WHERE detail.com_name   = rawdata.com_name
AND   detail.com_number = rawdata.com_number;

INSERT INTO company_rawdata (com_category_id)
SELECT com_category_id FROM company_category AS cat, 
                            company_rawdata AS raw
WHERE cat.com_category  = rawdata.com_category;

INSERT INTO company_rawdata (com_status_id)
SELECT com_status_id FROM company_status AS status, 
                          company_rawdata AS raw
WHERE status.com_status = rawdata.com_status;

I'm trying write a query to combine three insert with select query to insert these primary key from three different tables that match values with raw data and insert into company table.

Appreciate if you guys could provide a helping hand solve my question. Thanks for your time and consideration.

Answer 1

with your updatedd details I assume you are looking for:

INSERT INTO company (com_detail_id, com_category_id, com_status_id)
SELECT com_detail_id, com_category_id, com_status_id
FROM company_rawdata 
JOIN company_detail ON detail.com_name = rawdata.com_name AND detail.com_number = rawdata.com_number
JOIN company_category ON cat.com_category = rawdata.com_category
JOIN company_status  ON status.com_status = rawdata.com_status
;