>>>>>Hi,
>>>>>
>>>>>How I would create a SQL Select for the following case:
>>>>>Table 1
>>>>>
>>>>>PART_NO PART_PK PART_QTY
>>>>>"123" 1 34
>>>>>"ABC" 2 0
>>>>>"NBZ" 3 12
>>>>>
>>>>>
>>>>>Table 2
>>>>>
>>>>>PART_PK QTY
>>>>>1 3
>>>>>1 4
>>>>>1 10
>>>>>3 1
>>>>>2 6
>>>>>3 2
>>>>>2 35
>>>>>
>>>>>
>>>>>Resulting query should be
>>>>>
>>>>>PART_NO PART_QTY ACT_QTY (Act Qty is SUM of QTY in Table 2 field QTY)
>>>>>"123" 34 17
>>>>>"ABC" 0 41
>>>>>"NBZ" 12 3
>>>>>
>>>>>
>>>>>TIA
>>>>>
>>>>>UPDATE: I know that the SQL Select should consist of two SQL Select:
>>>>>
>>>>>select PART_NO, PART_QTY ..... (SELECT SUM(QTY) AS ACT_QTY from Table2 GROUP BY PART_PK) AS ACT_QTY FROM Table1
>>>>>
>>>>>
>>>>>But I don't know how to "connect" the main SQL Select with the SUB-SQL Select
>>>>
>>>>Dmitry,
>>>>
>>>>A single statement should suffice, I think:
>>>>
>>>>
>>>>SELECT T1.PART_NO, T1.PART_QTY, SUM(T2.QTY) ;
>>>> FROM T1 INNER JOIN T2 ON T2.PART_PK = T1.PART_PK ;
>>>> GROUP BY 1, 2
>>>>
>>>
>>>Antonio,
>>>
>>>First, thank you very much.
>>>Your approach is much simpler, of course. But I also need several other fields from the T1 (e.g. PART_DESCR). So, would I have to use MAX(T1.PART_DESCR) as PART_DESCR to get those "extra" fields?
>>
>>Dmitry,
>>
>>This will give all the fields from first table.
>>
>>
>>SELECT T1.*, TS.QTY ;
>> FROM T1 INNER JOIN (SELECT PART_PK, SUM(QTY) AS QTY FROM T2 GROUP BY PART_PK) TS ON TS.PART_PK = T1.PART_PK
>>
>
>Antonio,
>Thank you very much!
>
>Last question, please. If I wanted to select only those records from T1 that haveT1.PART_QTY different from the TS.QTY, I would have to add the HAVING, right? As, off the top:
>
>
>SELECT T1.*, TS.QTY ;
> FROM T1 INNER JOIN (SELECT PART_PK, SUM(QTY) AS QTY FROM T2 GROUP BY PART_PK) TS ON TS.PART_PK = T1.PART_PK HAVING TS.QTY <> T1.PART_QTY
>
Don't mention it, Dmitry, it's always a pleasure.
Using
AND instead of
HAVING would do the trick, in this case, and I suspect (and it's only a suspicion) that it would escalate better.
----------------------------------
António Tavares Lopes