CODEWITHSUNDEEP
sqlsql-server
Arnaud Stephan
Arnaud Stephan

Reputation: 401

Strange type of union

Sorry for the strange title, I don't have much experience with SQL. I'm working with 2 tables : table COMPONENTS, that lists some components, and table OPERATIONS that is a list of operations applied to components.

Table COMPONENTS looks like that :

+-------+--------------+-------------------+-------+
| Level | Main Article | Secondary Article | Brand |
+-------+--------------+-------------------+-------+
|     1 | Article A    | Article 1         | Foo   |
|     1 | Article B    | Article 1         | Bar   |
+-------+--------------+-------------------+-------+

It has a lot more columns, but this is the idea. Basically it lists all articles in stock and what articles they are made of.

The OPERATIONStable contains all the operations a secondary article has to undergo to be incorporated into a main one :

+--------------------+-----------+------+
| Secondary Article  | Operation | Cost |
+--------------------+-----------+------+
| Article 1          | Cutting   | X    |
| Article 1          | Knitting  | Y    |
| Article 1          | Bleaching | Z    |
+--------------------+-----------+------+

What would be the best way to combine those two tables into one that would have this structure ?

+-------+--------------+-------------------+-----------+------+-------+
| Level | Main Article | Secondary Article | Operation | Cost | Brand |
+-------+--------------+-------------------+-----------+------+-------+
|     1 | Article A    | Article 1         |           |      | Foo   |
|     1 | Article A    | Article 1         | Cutting   | X    | Foo   |
|     1 | Article A    | Article 1         | Knitting  | Y    | Foo   |
|     1 | Article A    | Article 1         | Bleaching | Z    | Foo   |
|     1 | Article B    | Article 1         |           |      | Bar   |
|     1 | Article B    | Article 1         | Cutting   | X    | Bar   |
|     1 | Article B    | Article 1         | Knitting  | Y    | Bar   |
|     1 | Article B    | Article 1         | Bleaching | Z    | Bar   |
+-------+--------------+-------------------+-----------+------+-------+

I tried to simplify the problem as much as possible. How should I proceed to manage this ? I tried with joins and unions, but it doesn't work.

It seems to me that this would be super easy to do with a programming language that allows loops, but I'm completely lost here.

Upvotes: 0

Views: 48

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

This seems like a union all with join:

select c.Level, c.MainArticle, c.SecondaryArticle, 
       NULL as operation, NULL as cost, c.brand
from components c
union all
select c.level, c.MainArticle, c.SecondaryArticle,
       o.operation, o.cost, c.brand
from components c join
     operations o
     on c.SecondaryArticle = o.SecondaryArticle;

If you care about the ordering in the result set, use:

order by MainArticle, SecondaryArticle

and whatever other keys you might want.

You could also do the union all before the join, although this might affect performance:

select c.level, c.MainArticle, c.SecondaryArticle,
       o.operation, o.cost, c.brand
from components c join
     ((select o.SecondaryArticle, o.operation, o.cost
       from operations o
      ) union all
      (select c.SecondaryArticle, NULL, NULL
       from components c
      )
     ) o
     on c.SecondaryArticle = o.SecondaryArticle;

Upvotes: 0

Thom A
Thom A

Reputation: 95606

Rather than using a UNION I would do something like this:

SELECT C.Level,
       C.MainArticle,
       C.SecondaryArticle,
       O.Operation,
       O.Cost,
       C.Brand
FROM COMPONENTS C
     CROSS APPLY (VALUES(0),(1)) V(Header)
     LEFT JOIN OPERATIONS O ON V.Header = 1
                           AND C.SecondaryArticle = O.SecondaryArticle
ORDER BY C.MainArticle,
         V.Header,
         O.Cost;

db<>fiddle

Upvotes: 3

TheWildHealer
TheWildHealer

Reputation: 1616

Your join/union intuition was right, you can do something like this:

select
    Level,
    `Main Article`,
    `Secondary Article`,
    '' as Operation,
    '' as Cost,
    Brand
from
    COMPONENTS
union select
    COMPONENTS.Level,
    COMPONENTS.`Main Article`,
    COMPONENTS.`Secondary Article`,
    OPERATIONS.Operation,
    OPERATIONS.Cost,
    COMPONENTS.Brand
from
    COMPONENTS
    left join OPERATIONS on COMPONENTS.`Secondary Article` = OPERATIONS.`Secondary Article`

Upvotes: 0

Related Questions