Cant figure out how to pull data from one table but get diffrent results

  • RGSjjarrett

    SSC Rookie

    Points: 33

    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

  • sgmunson

    SSC Guru

    Points: 110503

    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)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

  • pietlinden

    SSC Guru

    Points: 62848

    Looks like you need two instances of TB_EMP to join on the welder key and another to join on the Operator key.

  • sgmunson

    SSC Guru

    Points: 110503

    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)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

  • RGSjjarrett

    SSC Rookie

    Points: 33

    I have created a Excel Doc with dummy info to show how the fields look in the tables.

    After running the query i would like to see the initials for the Welder and the Operator.

    Attachments:
    You must be logged in to view attached files.
  • pietlinden

    SSC Guru

    Points: 62848

    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.

  • pietlinden

    SSC Guru

    Points: 62848

    Oh, I see now... how about something like this:

    CREATE TABLE Employee (
    EmployeeKey INT IDENTITY PRIMARY KEY,
    FirstName VARCHAR(20) NOT NULL,
    LastName VARCHAR(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 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply