Skip to content
Advertisement

How do I join two tables from a database in SQL?

I’m currently working on a project where we have to join two tables in SQL and then create a page to show the result of the two tables combined.

This is what I have so far:

SELECT *
FROM
    ANIMAL
    LEFT OUTER JOIN
    FOOD_PORTION
ON
    ANIMAL = FOOD_PORTION

and then a second page where the outcome should be:

CREATE TABLE ANIMAL(
    AnimalID        CHAR(5)         PRIMARY KEY,
    AnimalName      CHAR(50)        NOT NULL,
    Species         CHAR(50)        NOT NULL,
    Weight          INT             NOT NULL,
    DOB             DATE            NOT NULL,
    ExhibitID       CHAR(5)         REFERENCES EXHIBIT(ExhibitID)
);


CREATE TABLE FOOD_PORTION(
    PortionSize     INT             NOT NULL,
    AnimalID        CHAR(5)         REFERENCES ANIMAL(AnimalID)
);

SELECT C.Name
FROM   ANIMAL AS C
UNION
SELECT S.Name
FROM   FOOD_PORTION AS S

Advertisement

Answer

This should give you what you need. Using tadman’s suggestion to change the char column to INT. Also changing the CHAR to VARCHAR columns.

I also made the ID’s in each table Identity columns (so they will auto populate).

CREATE TABLE ANIMAL(
    AnimalID        INT PRIMARY KEY IDENTITY,
    AnimalName      VARCHAR(50)        NOT NULL,
    Species         VARCHAR(50)        NOT NULL,
    Weight          INT             NOT NULL,
    DOB             DATE            NOT NULL,
    ExhibitID       CHAR(5)         REFERENCES EXHIBIT(ExhibitID)
);


CREATE TABLE FOOD_PORTION(
    PortionSize     INT IDENTITY,
    AnimalID        INT         REFERENCES ANIMAL(AnimalID)
);

SELECT A.*, FP.*
FROM Animal A 
INNER JOIN Food_Portion FP ON A.AnimalID = FP.AnimalID
10 People found this is helpful
Advertisement