CODEWITHSUNDEEP
sqlsql-server
Joe Cobain
Joe Cobain

Reputation: 91

How to make an inner join have multiple joins for the same table

I am new to SQL and I am currently doing a project for uni.

Background Information:

CREATE TABLE Folders (
    Folder_ID INT PRIMARY KEY IDENTITY,
    FolderName VARCHAR (60) NOT NULL,
    DateCreated DATE NOT NULL,
    DateUpdated DATE,
    UserAccounts INT NOT NULL,
    EmailID INT,
);  

CREATE TABLE Emails (
    MessageID  INT PRIMARY KEY IDENTITY,
    Reciever INT NOT NULL,
    Sender INT NOT NULL,
    Attachments VARCHAR (60),
    Labels INT,
    Folders INT,
    Contents VARCHAR (1500) NOT NULL,
    EmailDate DATETIME DEFAULT(GETDATE()), -- Looked at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=15990 for the solution to get the date and time automatically (rather than just the date)
    Message_Subject VARCHAR (60),
    BCC VARCHAR (60),
    CC VARCHAR (60),
    Message_Priority VARCHAR (60),
    EmailType INT NOT NULL,
    UserAccountID INT NOT NULL,
    CONSTRAINT fk_Email_Type FOREIGN KEY (EmailType) REFERENCES TypeOfEmail (EmailType_ID),
    CONSTRAINT fk_UserAccount FOREIGN KEY (UserAccountID) REFERENCES UserAccount (UserAccount_ID),
    CONSTRAINT fk_Label FOREIGN KEY (Labels) REFERENCES Labels (Label_ID),
    CONSTRAINT fk_FoldersID FOREIGN KEY (Folders) REFERENCES Folders (Folder_ID),
);
CREATE TABLE TypeOfEmail (
    EmailType_ID INT PRIMARY KEY IDENTITY,
    Type VARCHAR (8) NOT NULL,
);

The inner join to connect the EmailType in the Email table and the type in the TypeOfEmail table:

SELECT * FROM Emails INNER JOIN TypeOfEmail ON Emails.EmailType = TypeOfEmail.EmailType_ID

All I want to do is also inner join Folders in the Email table with FolderName in the Folders table in the same inner join statement.

Can anyone tell me what statement i would use?

Hope this is clear :/

Upvotes: 0

Views: 38

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

Just Join the Folders table using the On condition you have mentioned.

SELECT E.required_col,T.required_col,F.required_col -- likewise use appropriate alias and select the column
FROM   Emails E
       INNER JOIN TypeOfEmail T
               ON E.EmailType = T.EmailType_ID
       INNER JOIN Folders F
               ON E.Folders = F.FolderName

Upvotes: 2

Related Questions