0

I use MS-SQL and have the following two tables, where I want to extract the values to create the third table within one SQL query.

The answer from here helped me, but it's not enough.

sql sum data from multiple tables

Also it is important for me, that when there is no deregistration or no registration but a deregistration (fautly) I have to get the value 0 for that, so I need to have also results without a hit in both tables, but only in one.

Is it possible to get this result within one SQL query?

REG

[ATE]   [Article]  [Description] [Registered]     
------------------------------------------
[1111]  [MS1234]      [SCREW]     [500]  
[2222]  [MS1234]      [SCREW]     [600]  
[2222]  [AB9999]       [BOLT]     [100]  
[3333]  [XYZ123]      [PAINT]     [900]  
[4444]  [LMU232]       [TOOL]     [120]

DEREG

[ATE]   [Article]  [Description] [Deregistered]     
------------------------------------------
[1111]  [MS1234]      [SCREW]     [260]  
[3333]  [XYZ123]      [PAINT]     [900]  
[4444]  [LMU232]       [TOOL]      [20]  
[9999]  [TUM933]       [SHIM]    [5000]

RES

[ATE]   [Article]  [Description] [Registered] [Deregistered]    [Rest]
--------------------------------------------------------------------------
[1111]  [MS1234]      [SCREW]        [500]        [260]         [240]
[2222]  [MS1234]      [SCREW]        [600]          [0]         [600]
[2222]  [AB9999]       [BOLT]        [100]          [0]         [100]
[3333]  [XYZ123]      [PAINT]        [900]        [900]           [0]
[4444]  [LMU232]       [TOOL]        [120]         [20]         [100]
[9999]  [TUM933]       [SHIM]          [0]       [5000]       [-5000]
GMB
  • 216,147
  • 25
  • 84
  • 135
Manfred Singer
  • 169
  • 13

1 Answers1

1

In SQL Server, you can full outer join to get records coming from both tables:

select 
    coalesce(r.ate, d.ate) ate, 
    coalesce(r.article, d.article) article, 
    coalesce(r.description, d.description) description, 
    coalesce(r.registered, 0) registered,
    coalesce(d.deregistered, 0) deregistered,
    coalesce(r.registered, 0) - coalesce(d.deregistered, 0) rest
from reg r
full outer join dereg d on d.ate = r.ate and d.article = r.article

Demo on DB Fiddle:

 ate | article | description | registered | deregistered |  rest
---: | :------ | :---------- | ---------: | -----------: | ----:
1111 | MS1234  | SCREW       |        500 |          260 |   240
2222 | MS1234  | SCREW       |        600 |            0 |   600
2222 | AB9999  | BOLT        |        100 |            0 |   100
3333 | XYZ123  | PAINT       |        900 |          900 |     0
4444 | LMU232  | TOOL        |        120 |           20 |   100
9999 | TUM933  | SHIM        |          0 |         5000 | -5000
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Unfortunatelly I get the message "sql missing operator in query expression 'coalesce(r.ate, d.ate) ate' – Manfred Singer Dec 10 '19 at 12:56
  • @ManfredSinger: I just fixed a typo (missing comma), is it working now? – GMB Dec 10 '19 at 13:47
  • I saw this already and tried it with the comma, but it doesnt work. – Manfred Singer Dec 10 '19 at 15:08
  • @ManfredSinger: this seems to work fine. I added a db fiddle to my answer for your reference. Are you using ms-access (which the error message seems to indicate), or sql-server (which you tagged your question with)? `full join` is not supported in ms-access. – GMB Dec 10 '19 at 15:18
  • In this case I use ODBC for Excel. Inner join works, but not full outer join – Manfred Singer Dec 11 '19 at 11:08