I have 4 tables and im trying to join them. It doesnt work yet.

Question

0.00/5 (No votes)

See more:

Hello community.

I have 4 tables and Im trying to join them. it doesnt work yet. I need to join that user can retrieve the Engineer’s Lastname and Firstname, the Faculty Lastname and Email and the Classes’s Subject and Title for each Course enrollment.

CREATE TABLE Engineers(
EID NUMBER,
Lastname varchar2(50),
Firstname varchar2(50),
Email varchar2(50),
Graddate DATE,
PRIMARY KEY(EID)
);

CREATE TABLE Faculty(
FID NUMBER,
Lastname varchar2(50),
Firstname varchar2(50),
Email varchar2(50),
Hiredate DATE,
PRIMARY KEY(FID)
);

CREATE TABLE Classes(
CID NUMBER,
Subject varchar2(50),
Catalognbr varchar2(50),
Title varchar2(50),
PRIMARY KEY(CID)
);

CREATE TABLE ClassEnrollments(
EnID NUMBER PRIMARY KEY,
EID NUMBER NOT NULL,
FID NUMBER NOT NULL,
CID NUMBER NOT NULL,
CONSTRAINT fk_engineers
FOREIGN KEY (EID)
REFERENCES Engineers(EID) ON DELETE CASCADE,
CONSTRAINT fk_faculty
FOREIGN KEY (FID)
REFERENCES Faculty(FID) ON DELETE CASCADE,
CONSTRAINT fk_classes
FOREIGN KEY (CID)
REFERENCES Classes(CID) ON DELETE CASCADE
);
and that the query that I came up with:

What I have tried:

SELECT
EnID,
Engineer.Lastname AS Lastname,
Engineer.Firstname AS Firstname,

Faculty.Lastname AS Flastname,
Faculty.Email AS email,
Classes.Subject AS Subject,
Classes.Title AS Title,
FROM
ClassEnrollments
INNER JOIN Engineer ON Engineer.EID = ClassEnrollments.EID
INNER JOIN Faculty ON Engineer.FID = ClassEnrollments.FID
INNER JOIN Classes ON Engineer.CID = ClassEnrollments.CID;

Posted 16-Aug-19 4:01am

Sergey2121

Updated 16-Aug-19 19:15pm

Add a Solution

Comments

OriginalGriff 16-Aug-19 10:13am

And?
What does it do that you didn't expect, or not do that you did?
What did you try to fix it?
Where are you stuck?
What help do you need?

[no name] 16-Aug-19 10:16am

Join them "two" at a time: (((A + B) + C) + D)

Sergey2121 16-Aug-19 10:17am

I tried to do JOIN however it doesnt work at all, I think I might be doing something wrong. Could it be that I use wrong join schema or something? I was following tutorials but cant comprehend multiple(more then 2) table joins.

[no name] 16-Aug-19 10:23am

Just JOIN ... at the end you will only have "engineers with records in all four tables.

INNER and OUTER will creat NULLS here and there when records are "missing".

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2019-08-16T04:47:00

When you get a problem like this, start by eliminating possibilities.
Comment out most of the query, and see what you get:

SQL

SELECT
EnID
--,Engineer.Lastname AS Lastname
--,Engineer.Firstname AS Firstname
--,Faculty.Lastname AS Flastname
--,Faculty.Email AS email
--,Classes.Subject AS Subject
--,Classes.Title AS Title
FROM
ClassEnrollments
--INNER JOIN Engineer ON Engineer.EID = ClassEnrollments.EID
--INNER JOIN Faculty ON Engineer.FID = ClassEnrollments.FID
--INNER JOIN Classes ON Engineer.CID = ClassEnrollments.CID;

If that produces records, add a bit back:

SQL

SELECT
EnID
,Engineer.Lastname AS Lastname
,Engineer.Firstname AS Firstname
--,Faculty.Lastname AS Flastname
--,Faculty.Email AS email
--,Classes.Subject AS Subject
--,Classes.Title AS Title
FROM
ClassEnrollments
INNER JOIN Engineer ON Engineer.EID = ClassEnrollments.EID
--INNER JOIN Faculty ON Engineer.FID = ClassEnrollments.FID
--INNER JOIN Classes ON Engineer.CID = ClassEnrollments.CID;

And see what that produces. If it all looks good, add some more:

SQL

SELECT
EnID
,Engineer.Lastname AS Lastname
,Engineer.Firstname AS Firstname
,Faculty.Lastname AS Flastname
,Faculty.Email AS email
--,Classes.Subject AS Subject
--,Classes.Title AS Title
FROM
ClassEnrollments
INNER JOIN Engineer ON Engineer.EID = ClassEnrollments.EID
INNER JOIN Faculty ON Engineer.FID = ClassEnrollments.FID
--INNER JOIN Classes ON Engineer.CID = ClassEnrollments.CID;

And so on. When it fails, look closely at the data you just added and see if there is anything obvious.

Salman622 · Answer 2 · 2019-08-16T19:16:00

I think the problems is with your join conditions
you are again and again referencing engineer.column with your classenrollment.column

try below query:

SELECT
EnID,
Engineer.Lastname AS Lastname,
Engineer.Firstname AS Firstname,

Faculty.Lastname AS Flastname,
Faculty.Email AS email,
Classes.Subject AS Subject,
Classes.Title AS Title,
FROM
ClassEnrollments
INNER JOIN Engineer ON Engineer.EID = ClassEnrollments.EID
INNER JOIN Faculty ON Faculty.FID = ClassEnrollments.FID
INNER JOIN Classes ON Classes.CID = ClassEnrollments.CID;

Gerry Schmitz · Answer 3 · 2019-08-16T04:28:00

Solution 1

Confirm there is a record for each engineer in EVERY table.

Posted 16-Aug-19 4:28am

Gerry Schmitz

I have 4 tables and im trying to join them. It doesnt work yet.

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0