How to join multiple tables SQL together according to scenario-CodePudding

I am trying to write a query where I need to get

A list containing the name (surname and first name) of any employee that has picked any product(s) for a stock request.

I am confused about this as all this information is scattered amongst different tables.

The contents of my tables are as follows;

Picking_List (warehouseID, locationID, productNum, requestNum, quantityPicked, datePicked, pickerStaffID)

INSERT INTO Picking_List (warehouseID, locationID, productNum, requestNum, quantityPicked, datePicked, pickerStaffID)
    VALUES ('1004','123','10','154','2','10/10/2021','4561');
INSERT INTO Picking_List (warehouseID, locationID, productNum, requestNum, quantityPicked, datePicked, pickerStaffID)
    VALUES ('1004','21','143','283','1','07/07/2021','1832');
INSERT INTO Picking_List (warehouseID, locationID, productNum, requestNum, quantityPicked, datePicked, pickerStaffID)
    VALUES ('1004','84','25','321','4','12/02/2020','3313');
INSERT INTO Picking_List (warehouseID, locationID, productNum, requestNum, quantityPicked, datePicked, pickerStaffID)
    VALUES ('1004','76','130','254','8','02/02/2019','2121');
INSERT INTO Picking_List (warehouseID, locationID, productNum, requestNum, quantityPicked, datePicked, pickerStaffID)
    VALUES ('1004','98','223','78','3','06/04/2021','1020');

Employee (StaffID, surname, firstName, dob, street, city, state, postcode, salary , WarehouseID, SupervisedBy)

INSERT INTO Employee (staffID,surname, firstName, dob, street, city, state, postcode, salary, warehouseID, supervisedBy)
    VALUES ('4561', 'Snow', 'John', '30/12/1995', 'vulture', 'Brisbane', 'QLD', '4010', '60,000', '1004', 'Nosheen');
INSERT INTO Employee (staffID,surname, firstName, dob, street, city, state, postcode, salary, warehouseID, supervisedBy)
    VALUES ('1832', 'Turner', 'Mike', '01/07/2000', 'vulture', 'Brisbane', 'QLD', '4010', '66,000', '1004', 'Nosheen');
INSERT INTO Employee (staffID,surname, firstName, dob, street, city, state, postcode, salary, warehouseID, supervisedBy)
    VALUES ('3313', 'Smith', 'Jake', '06/06/1996', 'vulture', 'Brisbane', 'QLD', '4010', '55,000', '1004', 'Nosheen');
INSERT INTO Employee (staffID,surname, firstName, dob, street, city, state, postcode, salary, warehouseID, supervisedBy)
    VALUES ('2121', 'Jones', 'Sally', '26/04/1984', 'vulture', 'Brisbane', 'QLD', '4010', '78,000', '1004', 'Nosheen');
INSERT INTO Employee (staffID,surname, firstName, dob, street, city, state, postcode, salary, warehouseID, supervisedBy)
    VALUES ('1020', 'Wilson', 'Andrew', '11/03/1980', 'vulture', 'Brisbane', 'QLD', '4010', '100,000', '1004', 'Nosheen');

Stock_Request (requestNum, requestDate, clientNum )

INSERT INTO Stock_Request (requestNum,requestDate, clientNum)
    VALUES ('123','10/12/2021','1234');
INSERT INTO Stock_Request (requestNum,requestDate, clientNum)
    VALUES ('141','21/08/2020','134');
INSERT INTO Stock_Request (requestNum,requestDate, clientNum)
    VALUES ('15','05/02/2021','434');
INSERT INTO Stock_Request (requestNum,requestDate, clientNum)
    VALUES ('223','11/03/2019','472');
INSERT INTO Stock_Request (requestNum,requestDate, clientNum)
    VALUES ('535','10/10/2020','1402');

Request_List (requestNum , productNum, qtyRequested )

INSERT INTO Request_List (requestNum, productNum, qtyRequested)
    VALUES ('525','232','2');
INSERT INTO Request_List (requestNum, productNum, qtyRequested)
    VALUES ('111','104','1');
INSERT INTO Request_List (requestNum, productNum, qtyRequested)
    VALUES ('145','32','5');
INSERT INTO Request_List (requestNum, productNum, qtyRequested)
    VALUES ('674','100','10');
INSERT INTO Request_List (requestNum, productNum, qtyRequested)
    VALUES ('5','22','4';

so far this is what I have got.

SELECT   C.surname,
         C.firstName, CA.pickerStaffID
  
FROM     employee AS C INNER JOIN Picking_List AS CA
ORDER BY employee ASC;

I am unsure if I have included everything I need in my query as per the task. Any help or advice would be appreciated as I am relatively new to SQL.

CodePudding user response：

Try this:

SELECT 
    concat(e.surname, ', ', e.firstName) as fullName
FROM
    Picking_List pl
INNER JOIN
    Employee e ON e.StaffID = pl.pickerStaffID
WHERE
    pl.requestNum = :RequestNumberToLookFor

:RequestNumberToLookFor is the request Number of the request you are looking for. Use appropriate parameterization syntax for your code.