Join result of two queries in SQL Server without repeating records

Question

Join result of two queries in SQL Server without repeating records

Navigation

#1 by (0 votes)

0

Dear ones have a good day, my question is as follows.

I want to join the results of two queries that coincide in columns and data type, as a first option I came up with a UNION, my first query is the following:

SELECT      T1.ArrivalDate AS Llegada, T1.DepartureDate AS Salida, T3.AccountNo AS Folio,
            T6.RoomNo AS Habitacion, T2.GuestTotal AS Huespedes, T7.Name AS Tarifa
FROM        obooGuest AS T1 INNER JOIN
            obooBooking AS T2 ON T2.ID_Booking = T1.ID_Guest INNER JOIN
            oaccAccount AS T3 ON T3.ID_Account = T1.ID_Guest INNER JOIN
            otrnTrnAccount AS T5 ON T5.ID_Account = T1.ID_Guest INNER JOIN
            prCode AS T7 ON T1.ID_RateCode = T7.ID_Code LEFT OUTER JOIN
            rmRoom AS T6 ON T6.ID_Room = T1.ID_Room
UNION ALL
SELECT        T2.StartDate AS Llegada, T2.EndDate AS Salida, T2.AccountNo AS Folio, Habitacion = NULL, T2.GuestTotal AS Huespedes, T4.Name AS Tarfia
FROM            prCode AS T4 INNER JOIN
                         oaccTemplate AS T3 ON T4.ID_Code = T3.ID_RateCode RIGHT OUTER JOIN
                         obooGrp AS T1 LEFT OUTER JOIN
                         V_GrpAndAllot AS T2 ON T2.ID_GrpAllot = T1.ID_Grp ON T3.ID_Template = T1.ID_Grp

And the result of the query is:

My second query:

SELECT (SELECT CurrentHotelDate FROM prProperty) AS Llegada, (SELECT CurrentHotelDate FROM prProperty) AS Salida, T1.AccountNo, 'NA' AS Habitacion, '0' AS Huespedes, 'NA' AS Tarifa
FROM oaccAccount AS T1

Results of the second consultation:

The point is that I want to filter the records of my second query, in the column "Folio" there are records that are already in the first query and I want to discard them so that the folio number and the records that do not repeat at any time keep those from the first consultation and discard those that come repeated from the second.

sql sql-server query db

asked by Gio Gómez 15.09.2018 в 19:16

source

1 answer

InvalidHeader: Invalid return character or leading space in header: x-api-token How to know the cardinality of a matrix in c ++?

score 0 · Answer 1

Although there are several ways to deal with this, the one I like the most is the following:

SELECT      T1.ArrivalDate AS Llegada, T1.DepartureDate AS Salida, T3.AccountNo AS Folio,
            T6.RoomNo AS Habitacion, T2.GuestTotal AS Huespedes, T7.Name AS Tarifa
FROM        obooGuest AS T1 INNER JOIN
            obooBooking AS T2 ON T2.ID_Booking = T1.ID_Guest INNER JOIN
            oaccAccount AS T3 ON T3.ID_Account = T1.ID_Guest INNER JOIN
            otrnTrnAccount AS T5 ON T5.ID_Account = T1.ID_Guest INNER JOIN
            prCode AS T7 ON T1.ID_RateCode = T7.ID_Code LEFT OUTER JOIN
            rmRoom AS T6 ON T6.ID_Room = T1.ID_Room
UNION ALL
SELECT        T2.StartDate AS Llegada, T2.EndDate AS Salida, T2.AccountNo AS Folio, Habitacion = NULL, T2.GuestTotal AS Huespedes, T4.Name AS Tarfia
FROM            prCode AS T4 INNER JOIN
                         oaccTemplate AS T3 ON T4.ID_Code = T3.ID_RateCode RIGHT OUTER JOIN
                         obooGrp AS T1 LEFT OUTER JOIN
                         V_GrpAndAllot AS T2 ON T2.ID_GrpAllot = T1.ID_Grp ON T3.ID_Template = T1.ID_Grp
WHERE T3.AccountNo not in
(
SELECT      Folio
FROM        obooGuest AS T1 INNER JOIN
            obooBooking AS T2 ON T2.ID_Booking = T1.ID_Guest INNER JOIN
            oaccAccount AS T3 ON T3.ID_Account = T1.ID_Guest INNER JOIN
            otrnTrnAccount AS T5 ON T5.ID_Account = T1.ID_Guest INNER JOIN
            prCode AS T7 ON T1.ID_RateCode = T7.ID_Code LEFT OUTER JOIN
            rmRoom AS T6 ON T6.ID_Room = T1.ID_Room
)

Simply to the second query you tell him not to include the folios of the first,

I hope you serve, greetings.