Using IN or EXISTS in SQL SERVER

Navigation
1

I'm doing a job for the university where I have to do a travel management system, where I have tables such as Pasajeros , Viaje , Boleto etc. Now I must make inquiries based on this basis that I created.

A particular query I could not make work, in this I have to list the data of the trains that have more than 20 seats which have not assigned any trip for tomorrow, which would be the column DATETIME FechaHoraViaje . 

DECLARE     @Fecha DATE

SET         @Fecha = DATEADD(DAY, +1, GETDATE())

SELECT      t.IdTren,
            t.TipoTren,
            t.CapacidadTren,
            t.MarcaTren,
            CAST(v.FechaHoraViaje AS DATE)

FROM        dbo.Tren        AS  t
JOIN        dbo.Viaje       AS  v   ON  v.IdTren = t.IdTren

WHERE       @Fecha IN (SELECT CAST(v.FechaHoraViaje AS DATE)
                       FROM dbo.Viaje AS v
                       WHERE CAST(v.FechaHoraViaje AS DATE) = '2017-06-08')

AND         CapacidadTren       > 20

GROUP BY    t.IdTren,
            t.TipoTren,
            t.CapacidadTren,
            t.MarcaTren,
            CAST(v.FechaHoraViaje AS DATE)

In the part of the WHERE use IN to compare my variable @Fecha with the result of the subquery, which would be exactly tomorrow's date. The result should be the information of the only train that has no trips assigned for tomorrow, but instead it returns the information of both trains, I do not understand what is the logic to return both trains.

    
asked by Gonzalo 09.06.2017 в 01:53
source

3 answers

0

The result you are getting is all the trains that have a trip for the date 2016-06-08 , that is what your query expresses when joining both tables and take into account the trips for that particular date by IN

If you want to get all the trains that do NOT have a trip for a given date (parameter @Date) that part of your query should be: 

WHERE NOT EXISTS (SELECT 1
                       FROM dbo.Viaje AS v
                       WHERE CAST(v.FechaHoraViaje AS DATE) = @Fecha and v.idTren = t.idTren)

This subquery would filter when for an idTren not referenced in the dbo table.Travel for the date 2017-06-08

Your query would be: 

DECLARE     @Fecha DATE

SET         @Fecha = DATEADD(DAY, +1, GETDATE())

SELECT      t.IdTren,
            t.TipoTren,
            t.CapacidadTren,
            t.MarcaTren,
            CAST(v.FechaHoraViaje AS DATE)

FROM        dbo.Tren        AS  t
JOIN        dbo.Viaje       AS  v   ON  v.IdTren = t.IdTren

  WHERE NOT EXISTS (SELECT 1
                           FROM dbo.Viaje AS v
                           WHERE CAST(v.FechaHoraViaje AS DATE) = @Fecha and 

AND         CapacidadTren       > 20

GROUP BY    t.IdTren,
            t.TipoTren,
            t.CapacidadTren,
            t.MarcaTren,
            CAST(v.FechaHoraViaje AS DATE)
    
answered by 09.06.2017 / 02:34
source
0

The problem is that you are not filtering properly in the subquery, since it does not verify if the current train of the data set has trains the next day, but there is no train with trips on the following day, the other that I see strange is that you want the list of trains, and that these leave only once, but you put the date of the trip, this makes the train leave more than once This is an example of 2 ways to do it. 

DECLARE     @Fecha DATE

SET         @Fecha = DATEADD(DAY, +1, GETDATE())

SELECT      t.IdTren,
            t.TipoTren,
            t.CapacidadTren,
            t.MarcaTren

FROM         @Tren       AS  t   
WHERE           CapacidadTren       > 20 and 
not exists(select 1 from @Viaje v1 where cast(v1.FechaHoraViaje as date)=@Fecha and t.IdTren = v1.IdTren)

SELECT      t.IdTren,
            t.TipoTren,
            t.CapacidadTren,
            t.MarcaTren


FROM         @Tren       AS  t
left JOIN        @Viaje       AS  v   ON  v.IdTren = t.IdTren   and cast(FechaHoraViaje as date)= @Fecha
WHERE           CapacidadTren       > 20 and 
v.IdViaje is null
    
answered by 09.06.2017 в 02:41
0

Observation 1 : A subquery is not required knowing that you are already doing JOIN to dbo.Viaje .

Observation 2 : When using DATEADD(DAY, +1, GETDATE()) , you are simply adding a day to the current date, that is, assuming that the current date and time are 2017/06/09 12:40:20 , adding one day to the current date you will get 2017/06/10 12:40:20 , then, if any trip leaves after that time will be out of the query.

The solution is to remove the subquery and put: 

WHERE       CAST(v.FechaHoraViaje AS DATE) = DATEADD(DAY, +1, CONVERT(DATE, GETDATE()))
AND         CapacidadTren       > 20

With this, the trips that meet these conditions would be selected.

Complete code (by the way, you do not need to declare any variables either): 

SELECT      t.IdTren,
            t.TipoTren,
            t.CapacidadTren,
            t.MarcaTren,
            CAST(v.FechaHoraViaje AS DATE)

FROM        dbo.Tren        AS  t
JOIN        dbo.Viaje       AS  v   ON  v.IdTren = t.IdTren

WHERE       CAST(v.FechaHoraViaje AS DATE) = DATEADD(DAY, +1, CONVERT(DATE, GETDATE()))
AND         CapacidadTren       > 20

GROUP BY    t.IdTren,
            t.TipoTren,
            t.CapacidadTren,
            t.MarcaTren,
            CAST(v.FechaHoraViaje AS DATE)
    
answered by 09.06.2017 в 19:02
Detect when rows are inserted and deleted in a Jquery table? ng-click does not work with Rows Grouping of Datatables