Create a SQL table using JOIN

Question

I split the original table into 3 tables, and now I want to create a table/view that is equivalent to the original table, but I couldn't get it work at all. Below:

CREATE TABLE BUSINESS_DATES
AS(
    SELECT
        ttxid, 
        dba_start_date, 
        dba_end_date, 
        location_start_date, 
        location_end_date
    FROM BUSINESSLOCATION
    ORDER BY dba_start_date
) WITH DATA;

CREATE TABLE BUSINESS_OWNERS
AS(
    SELECT
        ttxid, 
        certificate_number, 
        ownership_name, 
        dba_name
    FROM BUSINESSLOCATION
) WITH DATA;

CREATE TABLE BUSINESS_INFO
AS(
    SELECT 
        ttxid, 
        full_business_address, 
        city, 
        state, 
        business_zip, 
        mailing_address_1
    FROM BUSINESSLOCATION
) WITH DATA;

The SQL command I used to combine all tables:

CREATE VIEW ORIGINAL_TABLE
AS (
    SELECT *
    FROM BUSINESS_DATES AS BD
    LEFT JOIN BUSINESS_OWNERS AS BO
        ON BA.ttxid = BO.ttxid
) WITH DATA;

Don't use the `mysql` tag if you're not using MySQL. I've removed that tag, you should add the DBMS you're actually using. — Barmar, Jan 27 '22 at 02:16
@ProjectDestoryer, you wanted to combine all the tables into the view but you seem to have taken only two out of total three tables, there is no BUSINESS_INFO table included in your joins to create the original table, just include that and you are good to go. — GSM, Jan 27 '22 at 05:30

score 2 · Answer 1 · answered Jan 27 '22 at 02:19

This should work since the original tables are coming from BUSINESSLOCATION anyway.

CREATE VIEW ORIGINAL_TABLE AS(
    SELECT "ttxid", "dba_end_date", "location_start_date", "location_end_date", 
            "certificate_number", "ownership_name", "dba_name", "full_business_address",
            "city", "state", "business_zip", "mailing_address_1"
    FROM BUSINESSLOCATION
) WITH DATA;

Another option would be joining your three tables together on ttxid, similar to what you tried.

CREATE VIEW ORIGINAL_TABLE AS(
    SELECT *
    FROM BUSINESS_DATES BD
    LEFT JOIN BUSINESS_OWNERS BO ON BO.ttxid = BD.ttxid
    LEFT JOIN BUSINESS_INFO BI ON BI.ttxid = BD.ttxid
) WITH DATA;

Create a SQL table using JOIN

1 Answers1