>>>>>>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.
You are right. HAVING does not work as I get an error that it is not contained in either an aggregate function or the GROUP BY clause. I will go with the
AND. Again, thank you!
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham