July 31, 2020 at 4:59 pm
I have an Employee table that has a primary key. This key is used in another table for both a Welder column and a Operator Column.
How can i get the query to show two different results.
EX. we have a robots that weld. The welder would be RO1 but the operator would be NG.
here is the query i wrote.
SELECT TB_JOB.JOB_NAME,
TB_INSPECTIONS.INS_PN,
TB_INSPECTIONS.INS_Length,
TB_EMP.EMP_INT AS "WELDER",
TB_EMP.EMP_INT AS "OPERATOR",
TB_INSPECTIONS.INS_COMPLETION_DATE,
TB_INSPECTIONS.INS_PROBLEM
FROM TB_INSPECTIONS
JOIN TB_EMP ON TB_EMP.TB_EMP_KEY = TB_INSPECTIONS.INS_WELDER
JOIN TB_JOB ON TB_JOB.TB_JOB_KEY = TB_INSPECTIONS.TB_JOB_KEY
July 31, 2020 at 5:49 pm
Without further detail, I'm not sure what you mean when you say "How can i get the query to show two different results." Does that mean separate rows? As you've not posted any table DDL or sample data, we have no idea how your data is represented in the tables referred to in your query. If you can post some sample data, table DDL, and a detailed description of how many rows should appear per primary key value, we might then have a shot at actually helping. My gut says this might be a piece of cake, but without the details, there's no way to know what is actually needed. Help us help you...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 31, 2020 at 6:03 pm
Looks like you need two instances of TB_EMP to join on the welder key and another to join on the Operator key.
July 31, 2020 at 6:27 pm
Pietlinden,
Joining a second time isn't going to get another row... not that we know what the OP needs here, but just sayin' ...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 1, 2020 at 12:22 am
Well, I meant joining to that table twice and returning the two different employee names...
TB_EMP.EMP_INT AS "WELDER",
TB_EMP.EMP_INT AS "OPERATOR",
Oh wait... can't do that... because that's only one column in EMP aliased as two different values. I guess I was reading the post and assuming there were two values there instead of one. You'd have to have two columns in your table, one to store WelderID and the other to store OperatorID, or no joy.
August 1, 2020 at 1:05 am
Oh, I see now... how about something like this:
CREATE TABLE Employee (
EmployeeKey INT IDENTITY PRIMARY KEY,
FirstNameVARCHAR(20) NOT NULL,
LastNameVARCHAR(20) NOT NULL,
ShiftNo TINYINT NOT NULL
);
-- both WelderID and OperatorID refer to an Employee... so an EmployeeID in the Employee table.
CREATE TABLE Inspection (
InspectionID INT IDENTITY PRIMARY KEY,
JobName VARCHAR(25) NOT NULL,
WelderID INT,
OperatorID INT,
CompletionDate DATE,
Problem VARCHAR(50)
CONSTRAINT fkWelderID FOREIGN KEY (WelderID) REFERENCES Employee(EmployeeKey),
CONSTRAINT fkOperatorID FOREIGN KEY (OperatorID) REFERENCES Employee(EmployeeKey)
);
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply