This first part is a description of the real world situation I am dealing with building a report on inventory swaps where I have separated tables of reports linked to assets, tables with details about the assets, and tables of RMA Requests, but no direct relation to which assset was replaced by other asset only a vague relationship of the RMA orders linked to reports orders.

I have four tables.

  • table 1 represents reports of malfunctioning devices.
  • table 2 is an inventory table full of devices details, etc.
  • table 3 represents replacement orders.
  • table 4 represents the orders-replacements relations.

So records are like this.

table 1 are something like.
orderNo -  reported item 
1               1
1               2
2              56
2              34
2              23
3              15

table 2 
device id    lots of device detailed stuff in columns.
1              ...
2              ...
3              ...
...
15
23
34
56

table 3 (replacements)
rmaid   replacement
1           3
1           4
2           7
2           8
2           9
3           16

This is an example of the problem I am trying to resolve, and where my question emerge.

create table report(
    ID  int  not null,
    itemID int );

    insert into report (ID, ItemID)
    values (1,1),(1,2),(2,56),(2,34),(2,23),(3,15)

    create table device(
    ID int not null,
    dev_detail nvarchar(50) 
    );

    insert into device (ID, dev_detail)
    values (1,'det1'),(2,'det2'),(3,'det3'),(4,'det4'),(5,'det5'),
        (6,'det6'),(7,'det7'),(8,'det8'),(9,'det9'),(15,'det15'),
        (16,'det16'),(23,'dev23'),(34,'det34'),(56,'det56')

    create table replacement(
    id  int not null,
    ItemID int );

    insert into replacement (ID, ItemID)
    values (1,3),(1,4),(2,7),(2,8),(2,9),(3,16)

    create table [report-replacement](
    ID int not null,
    reportID int,
    replaceID int);

    insert into [report-replacement] (ID, reportID, replaceID)
    values (1,1,1),(2,2,2),(3,3,3)

    select 
    rep.ID as report_id,
    rep.itemID as reported_item,
    rep.dev_detail as reported_item_detail,
    rep.replaceID as report_replace_id,
    rma.id as rma_id,
    rma.ItemID as rma_item,
    rma.dev_detail as rma_item_detail

    from
    (
      select report.ID,report.itemID,device.dev_detail, replaceID
      from 
       report 
        inner join device on report.itemID = device.ID
        inner join [report-replacement] on reportID = [report-replacement].reportID
    ) as Rep
    inner join
    (
      select 
      replacement.id,replacement.ItemID,device.dev_detail
      from 
      replacement 
      inner join device on replacement.ItemID = device.ID
    ) as RMA
    on Rep.replaceID = RMA.id

    drop table report, device,replacement,[report-replacement]

This is the desired output, I would like help to build a on predicate join for the main inner join so that I get a 1 to 1 ratio of reported items to replaced items:

report id, item id, item details, rma_id, rma_item, rma_item_detail
1           1           det1        1        3          det3
1           2           det2        1        4          det4
2           56          det56       2        7          det7
2           34          det34       2        8          det8
2           23          det23       2        9          det9
3           15          det15       3       16          det16

This is the result of execution the example code, problem is I am getting a lot of rows and I am only using inner joins. There shouldn't be that many, and my question is if there is pecial join predicate, some clever way to get the desired output.

report_id   reported_item   reported_item_detail    report_replace_id   rma_id  rma_item    rma_item_detail
    1           1               det1                    1               1       3           det3
    1           2               det2                    1               1       3           det3
    2           56              det56                   1               1       3           det3
    2           34              det34                   1               1       3           det3
    2           23              dev23                   1               1       3           det3
    3           15              det15                   1               1       3           det3
    1           1               det1                    1               1       4           det4
    1           2               det2                    1               1       4           det4
    2           56              det56                   1               1       4           det4
    2           34              det34                   1               1       4           det4
    2           23              dev23                   1               1       4           det4
    3           15              det15                   1               1       4           det4
    1           1               det1                    2               2       7           det7
    1           2               det2                    2               2       7           det7
    2           56              det56                   2               2       7           det7
    2           34              det34                   2               2       7           det7
    2           23              dev23                   2               2       7           det7
    3           15              det15                   2               2       7           det7
    1           1               det1                    2               2       8           det8
    1           2               det2                    2               2       8           det8
    2           56              det56                   2               2       8           det8
    2           34              det34                   2               2       8           det8
    2           23              dev23                   2               2       8           det8
    3           15              det15                   2               2       8           det8
    1           1               det1                    2               2       9           det9
    1           2               det2                    2               2       9           det9
    2           56              det56                   2               2       9           det9
    2           34              det34                   2               2       9           det9
    2           23              dev23                   2               2       9           det9
    3           15              det15                   2               2       9           det9
    1           1               det1                    3               3       16          det16
    1           2               det2                    3               3       16          det16
    2           56              det56                   3               3       16          det16
    2           34              det34                   3               3       16          det16
    2           23              dev23                   3               3       16          det16
    3           15              det15                   3               3       16          det16

It's multiplying every record of the orders with every record of the replacements, and it's supposed to do only inner joins of two tables that previously joins with the device for getting details, so as you can see every device number gets its device details (16 gets det16 ).

I found that both joins RMA and Reps are both ok when joining with the device detail table, but the inner join of table reports with table [report-replacement] was not working properly so I changed the on clause order from reportID = [report-replacement].reportID to [report-replacement].reportID = reportID, and that decreased the rows from 36 to 14 rows.

1 Answers

2
Community On

This is the solution to my question.

select 
rep.ID as report_id,
rep.itemID as reported_item,
rep.dev_detail as reported_item_detail,
rep.replaceID as report_replace_id,
rma.id as rma_id,
rma.ItemID as rma_item,
rma.dev_detail as rma_item_detail,
Rep.seq,
rma.seq
from
(
  select report.ID,report.itemID,device.dev_detail, [report-replacement].replaceID,
  ROW_NUMBER() OVER (PARTITION BY report.id ORDER BY report.id) AS seq
  from 
   report 
    inner join device on device.ID = Report.ID
    inner join [report-replacement] on [report-replacement].reportID = Report.ID
) as Rep
inner join
(
  select
  replacement.id,replacement.ItemID,device.dev_detail,
  ROW_NUMBER() OVER (PARTITION BY replacement.id ORDER BY replacement.id) AS seq
  from 
  replacement 
  inner join device on device.ID = replacement.ItemID
) as RMA
on rep.replaceID = RMA.id AND rep.seq=rma.seq

The problem is commonly called cross-join by proxy.

Solution seems to be based on the use of ROW_NUMBER() OVER (PARTITION BY replacement.id ORDER BY replacement.id) AS seq