How to join tables without using join sql-CodePudding

Hi im trying to solve a sql querey where I need to grab information from multiple tables and output the end result.

A list of all clients that have not placed a stock request yet. Displaying client number will be sufficient.

I am not allowed to use inner join or any type of join to achieve this.

so far this is what I have come up with.

SELECT  c.clientNum
FROM    client AS C, Stock_Request AS SR
WHERE C.clientNum not in SR.ClientNum

this current attempt dosent achieve my disired result. here is the information from the tables.

(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');

(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);

any help would be appreciated.

CodePudding user response：

You can achieve it through this code:

SELECT clientNum FROM `client`
WHERE clientNum 
NOT IN (SELECT clientNum FROM stock_request GROUP BY clientNum);

You don't need to indicate the stock_request table on your main SELECT, you just need to use the stock_request table to fetch all the clientNum in a subquery by using IN, I think your using IN the wrong way.

Check more about MySQL IN Operator.