how to solve sql problem by accessing information from multiple tables to get end result-CodePudding

Hi I'm writing some queries for homework this week and I am stuck with this one. I need to gather information from multiple tables without using join

A list showing each product requested on each client stock request. Show client name, product number and quantity requested, sorted by client name and then product number.

my current code shows an output but dosent give the right results. it prints the contents of table 5 times instead of once as per the problem.

SELECT CLIENT.clientName, REQUEST_LIST.productNum, REQUEST_LIST.qtyRequested
FROM CLIENT, REQUEST_LIST 
WHERE CLIENT.clientNum IN (
    SELECT clientNum
    FROM STOCK_REQUEST)
    AND REQUEST_LIST.requestNum IN (
        SELECT requestNum
        FROM STOCK_REQUEST)
ORDER BY CLIENT.clientName , REQUEST_LIST.productNum

here are the tables:

(stock request) 
INSERT INTO Stock_Request (requestDate, clientNum)
    VALUES ('2020-12-10',1);
INSERT INTO Stock_Request (requestDate, clientNum)
    VALUES ('2020-05-04',2);
INSERT INTO Stock_Request (requestDate, clientNum)
    VALUES ('2021-07-06',3);
INSERT INTO Stock_Request (requestDate, clientNum)
    VALUES ('2021-07-08',4);
INSERT INTO Stock_Request (requestDate, clientNum)
    VALUES ('2022-02-07',5);


(request list) 
INSERT INTO Request_List (requestNum, productNum, qtyRequested)
    VALUES (1,1,1);
INSERT INTO Request_List (requestNum, productNum, qtyRequested)
    VALUES (2,2,2);
INSERT INTO Request_List (requestNum, productNum, qtyRequested)
    VALUES (3,3,3);
INSERT INTO Request_List (requestNum, productNum, qtyRequested)
    VALUES (4,4,4);
INSERT INTO Request_List (requestNum, productNum, qtyRequested)
    VALUES (5,5,5);

(client) 
INSERT INTO Client (clientName)
    VALUES ('Mike');
INSERT INTO Client (clientName)
    VALUES ('John');
INSERT INTO Client (clientName)
    VALUES ('Sally');
INSERT INTO Client (clientName)
    VALUES ('Margret');
INSERT INTO Client (clientName)
    VALUES ('Max');

any help would be appreciated.

expected output : ( not actual numbers )

client number |   product number | quantity requested

John Smith.        1.                3


Margret Thatcher.    3.                7

Max Payne.          6.                1

Mike Costello.      2.                1

Sally Barbarella.    4.               4

CodePudding user response：

First, you do not need to use subqueries to get clien_num and request_num values from stock_request table. Just use conditions in where clause of query. Row set is limited by stock_request records, row values allows to get appopriate values from client and request_list tables.

Second, i suggest you not to use casesensitive table and column names, it may lead errors by typos.

Finaly, your query would be like this

SELECT 
    client.client_name, 
    request_list.product_num, 
    request_list.qty_requested
FROM client, request_list, stock_request 
WHERE client.client_num = stock_request.client_num AND stock_request.request_num = request_list.request_num
ORDER BY client.client_name , request_list.product_num

Check fiddle https://www.db-fiddle.com/f/nFQ3s1GGEELCMoPBvPYwyk/1