how to change this sql query to sub-query

Question

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.

I tried this:

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

I get sth like this:

requestNum  clientNum   clientName  productNum  qtyRequested
2            2 [->]     David Liu       4            674
3            2 [->]     David Liu       5            66
1            1 [->]     Ian Peng        2            45
5            4 [->]     James Cameron   3            809
4            3 [->]     Mark Moris      1            164

But the requirement says we have to use sub-query, so I try to change it to:

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 AND REQUEST_LIST.productNum

But the result is not what I expect:

clientName  productNum  qtyRequested
David Liu       4           674
James Cameron   3           809
Mark Moris      5           66
David Liu       2           45
James Cameron   1           164
Mark Moris      4           674
James Cameron   5           66
Mark Moris      2           45
Ian Peng        3           809
James Cameron   4           674
Ian Peng        1           164
James Cameron   2           45
David Liu       3           809
Ian Peng        5           66
David Liu       1           164
Ian Peng        4           674
Mark Moris      3           809
David Liu       5           66
Ian Peng        2           45
Mark Moris      1           164

Shouldn't `ORDER BY CLIENT.clientName AND REQUEST_LIST.productNum` be `ORDER BY CLIENT.clientName, REQUEST_LIST.productNum`??? — cristian, Jun 06 '14 at 22:32
@Octopus-Paul - Yes, the current form (of either query) will throw a syntax error. For the OP - For the first query, you should be using [ANSI-style JOINs](http://stackoverflow.com/q/1018822/812837). In the second case you can combine the two subqueries like `WHERE (clientNum, requestNum) IN (SELECT clientNum, requestNum FROM STOCK_REQUEST)` — Clockwork-Muse, Jun 06 '14 at 22:42

how to change this sql query to sub-query

0 Answers0