CODEWITHSUNDEEP
sql-servert-sql
Jan Šalomoun
Jan Šalomoun

Reputation: 135

Second condition inside SELECT query

I have this simple query (TSQL) - 

SELECT IdTour, Tour, TourOrder, IdContent
    FROM View_ToursListContentId
    WHERE (IdContent = 3) OR (IdContent = 4)
    ORDER BY TourOrder DESC, Tour

Here is the result

58  Air & Style Tour                   2015/16  NULL    3
48  FIS Europa Cup                     2015/16  NULL    3
46  FIS World Cup Tour                 2015/16  NULL    3
131 KDC Grand Slam Regional Qualifiers 2016     NULL    3
59  Swatch Freeride World Tour         2016     NULL    3
77  World Rookie Tour                  2015/16  NULL    3
57  World Snowboard Tour               2015/16  NULL    3
45  X Games                            2016     NULL    3
45  X Games                            2016     NULL    4

What I need to do is. If ther is inside result IdTour with IdContent = 3 don't show the same IdTour with IdContent = 4. (Show IdTour with IdContent = 4 only, if there is not the same IdTour with IdContent = 3 - in my case show only one IdTour 45 with IdContent 3)

What is the easiest and the fastest way, how could I accomplish it?

Thanx a lot

Upvotes: 2

Views: 165

Answers (4)

GarethD
GarethD

Reputation: 69759

You could use a window function to obtain the lowest IdContent available for that IdTour:

SELECT  IdTour, 
        Tour, 
        TourOrder, 
        IdContent,
        MinIdContent = MIN(IdContent) OVER(PARTITION BY IdTour)
FROM    View_ToursListContentId
WHERE   IdContent IN (3, 4);

Then if this is 3 then you select only 3, if it is 4 then you select only 4, or more simply, only select IdContent that matches the minimum:

SELECT  IdTour, Tour, TourOrder, IdContent
FROM    (   SELECT  IdTour, 
                    Tour, 
                    TourOrder, 
                    IdContent,
                    MinIdContent = MIN(IdContent) OVER(PARTITION BY IdTour)
            FROM    View_ToursListContentId
            WHERE   IdContent IN (3, 4)
        ) AS t
WHERE   IdContent = MinIdContent;

FULL EXAMPLE

WITH View_ToursListContentId AS
(   SELECT  IdTour, Tour, TourOrder, IdContent
    FROM    (VALUES
                (58, 'Air & Style Tour 2015/16', NULL, 3),
                (48, 'FIS Europa Cup 2015/16', NULL, 3),
                (46, 'FIS World Cup Tour 2015/16', NULL, 3),
                (131, 'KDC Grand Slam Regional Qualifiers 2016', NULL, 3),
                (59, 'Swatch Freeride World Tour 2016', NULL, 3),
                (77, 'World Rookie Tour 2015/16', NULL, 3),
                (57, 'World Snowboard Tour 2015/16', NULL, 3),
                (45, 'X Games 2016', NULL, 3),
                (45, 'X Games 2016', NULL, 4)
            ) t (IdTour, Tour, TourOrder, IdContent)
)
SELECT  IdTour, Tour, TourOrder, IdContent
FROM    (   SELECT  IdTour, 
                    Tour, 
                    TourOrder, 
                    IdContent,
                    MinIdContent = MIN(IdContent) OVER(PARTITION BY IdTour)
            FROM    View_ToursListContentId
            WHERE   IdContent IN (3, 4)
        ) AS t
WHERE   IdContent = MinIdContent;

Upvotes: 0

maulik kansara
maulik kansara

Reputation: 1107

select * from (
select row_number() over (partition by IdTour, Tour, TourOrder order by IdContent) rownum,
IdTour, Tour, TourOrder, IdContent from View_ToursListContentId)src
where rownum=1

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

One way involves using NOT EXISTS:

SELECT IdTour, Tour, TourOrder, IdContent
FROM View_ToursListContentId t1
WHERE (IdContent = 3) OR 
      ((IdContent = 4) AND NOT EXISTS (SELECT 1
                                       FROM View_ToursListContentId t2
                                       WHERE t1.IdTour = t2.IdTour AND
                                             t2.IdContent = 3))
ORDER BY TourOrder DESC, Tour

The NOT EXISTS operator filters out records having IdContent = 4 in case a record with the same IdTour and IdContent = 3 exists.

Upvotes: 2

Rohit
Rohit

Reputation: 377

you should just use group by IdContent in query like 

SELECT IdTour, Tour, TourOrder, IdContent
    FROM View_ToursListContentId
    WHERE (IdContent = 3) OR (IdContent = 4)
group by IdContent
    ORDER BY TourOrder DESC, Tour

Upvotes: -1

Related Questions