Full Join N-N without repeating records

Question

I have two tables with relation N-N:

           ITEM_ORC                          ITEM_COMP

|   OP  |    Item Orçado   |    |   OP  |    NF   |   Item Comprado  |
+-------+------------------+    +-------+---------+------------------+
|   1   |      Lápis       |    |   1   |   101   |   Lapis Preto    |
|   1   |      Borracha    |    |   1   |   102   |   Caderno C Dura |
|   1   |      Caderno     |    |   2   |   102   |   Regua 15cm     |
|   2   |      Régua       |    |   2   |   102   |   Lousa Verde    |
|   2   |      Lousa       |    |   2   |   103   |   Caneta BIC Az  |

I need a query that brings the following result:

Relação Orçamento X Compras

|  OP  |   Item Orçado   |   NF    |    Item Comprado   |
+------+-----------------+---------+--------------------+
|  1   |     Lápis       |   101   |    Lapis Preto     |
|  1   |     Borracha    |   null  |    null            |
|  1   |     Caderno     |   102   |    Caderno C Dura  |
|  2   |     Régua       |   102   |    Regua 15cm      |
|  2   |     Lousa       |   102   |    Lousa Verde     |
|  2   |     null        |   103   |    Caneta BIC Az   |

Since there is no relationship between the items, a correct order between them is not necessary. I would just like the listing by OP, IE, could be that way the result, for example:

|  OP  |   Item Orçado   |   NF    |    Item Comprado   |
+------+-----------------+---------+--------------------+
|  1   |     Lápis       |   102   |    Caderno C Dura  |
|  1   |     Borracha    |   null  |    null            |
|  1   |     Caderno     |   101   |    Lapis Preto     |
|  2   |     Régua       |   102   |    Lousa Verde     |
|  2   |     Lousa       |   103   |    Caneta BIC Az   |
|  2   |     null        |   102   |    Regua 15cm      |

So far I’ve done the following query:

SELECT
   b.OP
   ,Item Orçado
   ,NF
   ,Item Comprado

FROM
(SELECT
   OP
   ,Item Orçado

FROM 
   ITEM_ORC) a

full join

(SELECT
   OP
   ,NF
   ,Item Comprado

FROM
   ITEM_COMP) b

ON a.OP = b.OP

But the result I get is that for each item budgeted returns me each one of the purchased:

|  OP  |   Item Orçado   |   NF    |    Item Comprado   |
+------+-----------------+---------+--------------------+
|  1   |     Lápis       |   101   |    Lapis Preto     |
|  1   |     Lápis       |   102   |    Caderno C Dura  |
|  1   |     Borracha    |   101   |    Lapis Preto     |
|  1   |     Borracha    |   102   |    Caderno C Dura  |
|  1   |     Caderno     |   101   |    Lapis Preto     |
|  1   |     Caderno     |   102   |    Caderno C Dura  |
|  2   |     Régua       |   102   |    Regua 15cm      |
|  2   |     Régua       |   102   |    Lousa Verde     |
|  2   |     Régua       |   103   |    Caneta BIC Az   |
|  2   |     Lousa       |   102   |    Regua 15cm      |
|  2   |     Lousa       |   102   |    Lousa Verde     |
|  2   |     Lousa       |   103   |    Caneta BIC Az   |

Just pointing out, the only relationship between the tables is the OP. The items, despite coincidences, have no relationship.

Can you help me? Thank you!

Answer 1

To get the expected result, what is missing is to add the condition Item Orçado = Item Comprado to the clause ON:

SELECT
   b.OP
   ,Item Orçado
   ,NF
   ,Item Comprado

FROM
(SELECT
   OP
   ,Item Orçado

FROM 
   ITEM_ORC) a

full join

(SELECT
   OP
   ,NF
   ,Item Comprado

FROM
   ITEM_COMP) b

ON a.OP = b.OP
AND Item Orçado = Item Comprado

I made a example in Sqlfiddle, yet using Postgresql.

---

Updating

According to comments and the author’s issue, there is no relationship between the items as in the example above.

Particularly I would not recommend doing a single query to bring the two information. But I can see a few cases where that might be useful, like in a simple report that shows the relationship between research and purchase, for example.

The answer @Runo meets the question well, but I will put here another query option a little different:

SELECT ISNULL(O.OP, C.OP) OP,  Item_Orcado, NF, Item_Comprado
FROM (
        SELECT OP, Item_Orcado, ROW_NUMBER() OVER (PARTITION BY OP ORDER BY Item_Orcado) AS Linha
        FROM ITEM_ORC
    ) O
FULL OUTER JOIN (
        SELECT OP, NF, Item_Comprado, ROW_NUMBER() OVER (PARTITION BY OP ORDER BY NF) AS Linha
        FROM ITEM_COMP
    ) C
    ON C.OP = O.OP 
    AND C.Linha = O.Linha
ORDER BY OP, ISNULL(NF, 'Z'), item_orcado

Note that the ISNULL in the sorting serves to play the NULLs to the end. This is interesting for reports.

See the new example in Sqlfiddle

Answer 2

A possible solution to your problem is to create a column that links the two tables. This example creates a pseudo relation within each OP.

;with pOrcado as (
   select op, [Item Orçado], row_number() over (partition by op, order by [Item Orçado]) as RN
   from ITEM_ORC
), pComprado as (
   select op, NF, [Item Comprado], row_number() over (partition by op, order by [Item Comprado]) as RN
   from ITEM_COMP    
) 
select isnull(o.op, c.op) as op, o.[Item Orçado], c.nif, c.[Item Comprado]
from pOrcado o
full outer join pComprado c
   on c.op = o.op
  and c.rn = o.rn