SQL Query retrieves data from one table and retrieves data from only one row from another table

Question

I don’t know how to explain my problem in the title, so I’ll explain it better here ...

I have two tables

CREATE TABLE [dbo].[Ventas] ( [IdVenta] [int] IDENTITY(1,1) NOT NULL, [FechaVenta] [date] NULL, [HoraVenta] [varchar](10) NULL, [Subtotal] [money] NULL, [Iva] [money] NULL, [Total] [money] NULL, [Saldo] [money] NULL, [Abono] [money] NULL, [FormaDePago] [varchar](50) NULL, [Plazos] [int] NULL, [Estado] [varchar](50) NULL, ) CREATE TABLE [dbo].[Plazos] ( [IdPlazo] [int] IDENTITY(1,1) NOT NULL, [IdVenta] [int] NULL, [NumeroPlazo] [int] NULL, [FechaVencimiento] [date] NULL, [FechaCorte] [date] NULL, [FechaPenalizacion] [date] NULL, [FechaLiquidacion] [date] NULL, [Total] [money] NULL, [Cargo] [money] NULL, [Abono] [money] NULL, [Estado] [varchar](50) NULL, ) 

now add some data

 INSERT [dbo].[Ventas] ([IdVenta], [FechaVenta], [HoraVenta], [Subtotal], [Iva], [Total], [Saldo], [Abono], [FormaDePago], [Plazos], [Estado]) VALUES (182, CAST(0x54360B00 AS Date), N'11:20', 500.0000, 55.0000, 555.0000, 333.0000, 222.0000, N'A Credito', 5, N'Pendiente De Pago') INSERT [dbo].[Ventas] ([IdVenta], [FechaVenta], [HoraVenta], [Subtotal], [Iva], [Total], [Saldo], [Abono], [FormaDePago], [Plazos], [Estado]) VALUES (183, CAST(0x54360B00 AS Date), N'12:29', 575.0000, 63.2500, 638.2500, 638.2500, 0.0000, N'Una Sola Exhibicion', 1, N'Pendiente De Pago') INSERT [dbo].[Plazos] ([IdPlazo], [IdVenta], [NumeroPlazo], [FechaVencimiento], [FechaCorte], [FechaPenalizacion], [FechaLiquidacion], [Total], [Cargo], [Abono], [Estado]) VALUES (93, 182, 1, CAST(0x54360B00 AS Date), CAST(0x57360B00 AS Date), CAST(0x5C360B00 AS Date), CAST(0x54360B00 AS Date), 111.0000, 0.0000, 111.0000, N'Liquidado') INSERT [dbo].[Plazos] ([IdPlazo], [IdVenta], [NumeroPlazo], [FechaVencimiento], [FechaCorte], [FechaPenalizacion], [FechaLiquidacion], [Total], [Cargo], [Abono], [Estado]) VALUES (94, 182, 2, CAST(0x73360B00 AS Date), CAST(0x75360B00 AS Date), CAST(0x7A360B00 AS Date), CAST(0x54360B00 AS Date), 111.0000, 0.0000, 111.0000, N'Liquidado') INSERT [dbo].[Plazos] ([IdPlazo], [IdVenta], [NumeroPlazo], [FechaVencimiento], [FechaCorte], [FechaPenalizacion], [FechaLiquidacion], [Total], [Cargo], [Abono], [Estado]) VALUES (95, 182, 3, CAST(0x91360B00 AS Date), CAST(0x94360B00 AS Date), CAST(0x99360B00 AS Date), NULL, 111.0000, 111.0000, 0.0000, N'Pendiente') INSERT [dbo].[Plazos] ([IdPlazo], [IdVenta], [NumeroPlazo], [FechaVencimiento], [FechaCorte], [FechaPenalizacion], [FechaLiquidacion], [Total], [Cargo], [Abono], [Estado]) VALUES (96, 183, 1, CAST(0x54360B00 AS Date), CAST(0x57360B00 AS Date), CAST(0x5C360B00 AS Date), NULL, 639.0000, 639.0000, 0.0000, N'Pendiente') 

Foreign key on Ventas.IdVenta = Plazos.IdVenta p>

Ok, here's the deal ... I need to use a query that displays data from all sales (Ventas), which should be only 2 rows ...

However, I need data from Plazos, but I only need data from Plazos. I need to display data from plazos on the same line as Ventas, but only data from the latest Plazos ...

you may notice that, for example, in Plazos there is a NumeroPlazo column that grows on the same IdVenta ... what I need in this example is to show:

Ventas IdVenta 182 with Plazos IdPlazo 95 data (starting with Plazos, IdPlazo 95 has the largest number in the Numero Plazos column ... and, of course, IdVenta 183, but since it has only one Plazo, it will display data from this plazo ...

At the moment, I had this request ...

 SELECT Ventas.*, Plazos.*, FROM Ventas INNER JOIN Plazos ON Plazos.IdVenta = Ventas.IdVenta WHERE Ventas.Estado = 'Pendiente De Pago' ORDER BY Ventas.FechaVenta DESC, Ventas.HoraVenta DESC 

but it returns 4 rows (3 rows for Venta, where IdVenta = 182, and one where IdVenta = 183) I want only 2 rows ...

Then I tried this query, which worked ... but for only one line

 SELECT Ventas.*, Plazos.*, FROM Ventas INNER JOIN Plazos ON Plazos.IdVenta = Ventas.IdVenta WHERE Ventas.Estado = 'Pendiente De Pago' AND Plazos.NumeroPlazo = (SELECT MAX(Plazos.NumeroPlazo) FROM Plazos WHERE Plazos.IdVenta = 182) ORDER BY Ventas.FechaVenta DESC, Ventas.HoraVenta DESC 

Obviously, it works only for one sale, since I specify Plazos.IdVenta = 182 ... My question is here ... how can I use the last query to get the data I want for each sale ...

I hope yuo can help me ... If you need me to be more specific, let me know.

Thank you in advance

Answer 1

You can use CROSS APPLY, which allows you to run a subquery on each row of previous tables in the FROM list.

 SELECT Ventas.*, Plazos.* FROM Ventas cross apply ( select TOP(1) * from Plazos WhERE Plazos.IdVenta = Ventas.IdVenta ORDER BY [NumeroPlazo] DESC) Plazos WHERE Ventas.Estado = 'Pendiente De Pago' ORDER BY Ventas.FechaVenta DESC, Ventas.HoraVenta DESC 

Doing this with Row_Number () is probably faster, but here the subquery will require you to nickname all the columns internally, which is probably not so bad.

 SELECT * FROM ( SELECT v.[IdVenta], v.[FechaVenta], v.[HoraVenta], v.[Subtotal], v.[Iva], v.[Total], v.[Saldo], v.[Abono], v.[FormaDePago], v.[Plazos], v.[Estado], p.[IdPlazo], p.[NumeroPlazo], p.[FechaVencimiento], p.[FechaCorte], p.[FechaPenalizacion], p.[FechaLiquidacion], p.[Total] plazostotal, p.[Cargo], p.[Abono] plazasabono, p.[Estado] plazosestado, RowN = ROW_NUMBER() over (partition by v.[IdVenta] order by p.[NumeroPlazo] desc) FROM Ventas v JOIN Plazos p ON p.IdVenta = v.IdVenta WHERE v.Estado = 'Pendiente De Pago' ) X WHERE RowN = 1 ORDER BY FechaVenta DESC, HoraVenta DESC 

Answer 2

It's simple. In the second question, replace 182 with Ventas.IdVenta p>

 SELECT Ventas.*, Plazos.* FROM Ventas INNER JOIN Plazos ON Plazos.IdVenta = Ventas.IdVenta WHERE Ventas.Estado = 'Pendiente De Pago' AND Plazos.NumeroPlazo = (SELECT MAX(Plazos.NumeroPlazo) FROM Plazos WHERE Plazos.IdVenta = Ventas.IdVenta) ORDER BY Ventas.FechaVenta DESC, Ventas.HoraVenta DESC