Full Join N-N without repeating records

Navigation
7

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 yields 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 items, a correct order between them is not required. I would just like the listing by OP, ie it 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 budgeted item I get each of the purchased ones: 

|  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 by pointing out, the only relationship between the tables is the OP. The items, despite coincidences, are unrelated.

Can you help me? Thanks!

    
asked by anonymous 23.04.2015 / 15:03

2 answers

7

To get the expected result, we need to add the condition Item Orçado = Item Comprado to the ON clause: 

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 , even though using PostgreSQL.

Update

According to comments and the issue Author's edit, 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. However, I can see a few cases where this can be useful, as in a simple report that shows the relationship between search and purchase, for example.

The answer @bruno answers the question well, but I'll 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 ISNULL in sorting is used to play NULL s to the end. This is interesting for reports.

See the new example in SqlFiddle

    
23.04.2015 / 16:03
2

A possible solution to your problem is to create a column that lists 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
    
27.04.2015 / 15:23
Percentage of equality of texts? Difference between Console.Read (); and Console.ReadLine ();