CODEWITHSUNDEEP
sqlsql-server
Tomasz Kaniewski
Tomasz Kaniewski

Reputation: 1195

UNION columns in one SELECT

Let's say :

SELECT Item.Id, Item.ParentId FROM Item ..."

Returns me this data:

Id |  ParentId
----------------
1  | NULL
2  | 17
3  | 13

Is there is a way to get this data as one column by using some kind of UNION but on columns from only one SELECT ? Something like:

SELECT (Item.Id UNION Item.ParentId) AS Id FROM Item...

Result :

Id |
----
1  | 
2  |
3  |
NULL
17 |
13 |

EDIT EXAMPLE:

I have Media Table:

Id |  ParentId
----------------
1  | NULL
2  | 1
3  | 2

It have relations with itself, this is some kind of 3 level tree structure (Series -> Seasons -> Episodes)

There is another Table Offer which contain information about availability:

Id |  MediaId  | Availability
------------------------------
1  | 3         | true

I need to get id's of all media that are available, but also all parent's id, of all levels.

I was thinking about: 

SELECT Media.Id, MediaSeason.Id, MediaSeries.Id  FROM Media
LEFT JOIN Media AS MediaSeason ON MediaSeason.Id = Media.ParentId
LEFT JOIN Media AS MediaSeries ON MediaSeries.Id = MediaSeason.ParentId 
LEFT JOIN Offer ON Offer.MediaId = Media.Id
WHERE Offer.Availability = true

This gives me all id's i need but in three different columns and I'm trying to find a way to put it into one, without repeating join and where login in 3 different SELECTS.

I'm using MSSQL.

Upvotes: 1

Views: 6005

Answers (4)

Maciej Skorski
Maciej Skorski

Reputation: 3384

Nested selects can be avoided in UNION

create table tab (
  Id int,
  ParentId int
);

insert into tab 
values
(1, NULL),
(2, 17),
(3, 13);

then do

select ID as ID
from tab
union all
select ParentId as ID
from tab

NOTE: DB queries can be conveniently tested live, e.g. http://sqlfiddle.com/#!17/7a3a8/2

Upvotes: 0

MatBailie
MatBailie

Reputation: 86808

SELECT DISTINCT
    pivot_hierarchy.media_id
FROM
    offers  o
LEFT JOIN
    media   m1
        ON m1.id = o.media_id
LEFT JOIN
    media   m2
        ON m2.id = m1.parent_id
OUTER APPLY
(
    SELECT o.media_id
    UNION ALL
    SELECT m1.parent_id WHERE m1.parent_id IS NOT NULL
    UNION ALL
    SELECT m2.parent_id WHERE m2.parent_id IS NOT NULL
)
    AS pivot_hierarchy
WHERE
    o.availability = 'true'

Everything up to the APPLY should be self explanatory. Get the offers, get the parent of that media if it has one, and the parent of that media if it has one.

The APPLY then joins each row on to a function that can return more than one row each. In this case the function returns 1, 2 or 3 rows. Those being the media id, it parent if it has one, and its grand-parent if it has one. To do that, the function unions the three input columns, provided that they’re not null.

This avoids having to join back on to the media table again.

Also, you need a distinct in the select. Otherwise the same series or season id could return multiple times.

Upvotes: 1

mnesarco
mnesarco

Reputation: 2798

If your children and parents are in the same table (Item)

SELECT Id FROM Item

Will retrieve all Items, including Parents because parents are also Items.

But if what you want is to not repeat the where clause and have Ids of any matched Media and its associated parents (even if the parent media does not match the where clause) you can try this:

SELECT 
    m.Id
FROM
    Media m INNER JOIN (
        SELECT 
            m2.Id, m2.ParentId
        FROM 
            Media m2
            LEFT JOIN Offer ON Offer.MediaId = m2.Id
        WHERE 
            Offer.Availability = true
    ) tmp ON (tmp.Id = m.Id OR tmp.ParentId = m.Id)

Finally, for three levels:

SELECT 
    m.Id
FROM
    Media m INNER JOIN (
        SELECT 
            m2.Id, m2.ParentId, m3.ParentId AS GrandParentId
        FROM 
            Media m2
            LEFT JOIN Media m3 ON m2.ParentId = m3.Id
            LEFT JOIN Offer ON Offer.MediaId = m2.Id
        WHERE 
            Offer.Availability = true
    ) tmp ON (tmp.Id = m.Id OR tmp.ParentId = m.Id OR tmp.GrandParentId = m.Id)

Upvotes: 2

MladenB
MladenB

Reputation: 161

Try this:

 SELECT * FROM (SELECT Item.Id FROM Item ...
    UNION ALL
    SELECT Item.ParentId FROM Item ...)

Upvotes: 5

Related Questions