CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Mary Pleorwer
Mary Pleorwer

Reputation: 1

Max of two columns from different tables

I need to get a max of the values from two columns from different tables. eg the max of suburbs from schoolorder and platterorder. platterorder has clientnumbers that links to normalclient, and schoolorder has clientnumbers that links to school.

I have this:

SELECT MAX (NC.SUBURB) AS SUBURB
FROM normalClient NC
WHERE NC.CLIENTNO IN
(SELECT PO.CLIENTNO
FROM platterOrder PO
WHERE NC.CLIENTNO = PO.CLIENTNO)
GROUP BY NC.SUBURB
UNION
SELECT MAX (S.SUBURB) AS SCHOOLSUBURB
FROM school S
WHERE S.CLIENTNO IN
(SELECT S.CLIENTNO
FROM schoolOrder SO
WHERE S.CLIENTNO = SO.CLIENTNO)
GROUP BY S.SUBURB)

However that gets the max from platter order and joins it with the max of school. what I need is the max of both of them together.

=================================================

sorry for making this so confusing!

the output should only be one row. it should be the suburb where the maxmimum orders have come from for both normal client and school clients. the orders are listed in platter order for normal clients, and school order for school clients. so it's the maximum value for two table's that don't have a direct relation.

hope that clears it up a bit !

Upvotes: 0

Views: 2743

Answers (1)

sgeddes
sgeddes

Reputation: 62831

If I'm understanding your question correctly, you don't need to use a GROUP BY since you're wanting the MAX of the field. I've also changed your syntax to use a JOIN instead of IN, but the IN should work just the same:

SELECT MAX (NC.SUBURB) AS SUBURB
FROM normalClient NC
    JOIN platterOrder PO ON NC.ClientNo = PO.ClientNo
UNION
SELECT MAX (S.SUBURB) AS SCHOOLSUBURB
FROM school S
    JOIN schoolOrder SO ON S.CLIENTNO = SO.CLIENTNO

Withouth knowing your table structures and seeing sample data, the best way I can recommend getting the MAX of results from the UNION is to use a subquery. There may be a better way with JOINs, but it's difficult to infer from your question:

SELECT MAX(Suburb)
FROM (
    SELECT MAX (NC.SUBURB) AS SUBURB
    FROM normalClient NC
        JOIN platterOrder PO ON NC.ClientNo = PO.ClientNo
    UNION
    SELECT MAX (S.SUBURB)
    FROM school S
        JOIN schoolOrder SO ON S.CLIENTNO = SO.CLIENTNO
) T

Upvotes: 4

Related Questions