Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Right join from grouped query
Message
From
06/04/2012 13:18:07
Victor Chignes
Inteliventas
Peru
 
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Environment versions
SQL Server:
SQL Server 2000
Application:
Desktop
Miscellaneous
Thread ID:
01540486
Message ID:
01540517
Views:
21
Thank you Naomi. The final code was as this:
SELECT b.cproveedor_id, a.ccompania_id as cia,'*' as oficina, 
a.cproveedor_id as Movi, b.cvehiculo_placa as Placa,b.cvehiculo_chofer as Chofer,
OrdersSum.clientes,OrdersSum.guia,OrdersSum.Ordenes,OrdersSum.peso
  FROM [bdmain].[dbo].[_proveedor] a,[ventas].[dbo].[_vehiculo] b
LEFT JOIN 
(select cid_transportista as cproveedor_id, COUNT (distinct cid_clie) As clientes,
	COUNT(*) as Ordenes ,SUM(npeso_total_documento) as peso,
	MAX(cnro_guia_remision) as guia
	from _movimiento_ventas where cestado_documento_id='I' and ctipo_documento_id in ('FA','BO','TK') group by cid_transportista
) OrdersSum ON b.cproveedor_id = OrdersSum.cproveedor_id
  where ctipo_proveedor_id='05'
and cestado_prov_id='A'
and a.cproveedor_id=b.cproveedor_id
>>Hello all.
>>I have a query which selects all vehicles from my database
>>
>>SELECT a.ccompania_id as cia,'*' as oficina, a.cproveedor_id as Movi, b.cvehiculo_placa as Placa,b.cvehiculo_chofer as Chofer
>>  FROM [bdmain].[dbo].[_proveedor] a,[ventas].[dbo].[_vehiculo] b
>>  where ctipo_proveedor_id='05'
>>and cestado_prov_id='A'
>>and a.cproveedor_id=b.cproveedor_id
>>
>>and another one wich summarizes all the customers and orders the vehicle has to deliver.
>>
>>select cid_transportista, COUNT (distinct cid_clie) As clientes,
>>	COUNT(*) as Ordenes ,SUM(npeso_total_documento) as peso,
>>	MAX(cnro_guia_remision) as guia
>>	from _movimiento_ventas where cestado_documento_id='I' and ctipo_documento_id in ('FA','BO','TK') group by cid_transportista
>>
>>
>>
>>I need to join both querys into one, but showing all the vehicles even if they don't have any order to deliver. I'm trying to do a right join but it does not seem to work on grouped tables. Any help?
>>TIA
>
>Try:
>
>SELECT a.ccompania_id as cia,'*' as oficina, a.cproveedor_id as Movi, b.cvehiculo_placa as Placa,b.cvehiculo_chofer as Chofer
>  FROM [bdmain].[dbo].[_proveedor] a,[ventas].[dbo].[_vehiculo] b
>LEFT JOIN 
>(select cid_transportista, COUNT (distinct cid_clie) As clientes,
>	COUNT(*) as Ordenes ,SUM(npeso_total_documento) as peso,
>	MAX(cnro_guia_remision) as guia
>	from _movimiento_ventas where cestado_documento_id='I' and ctipo_documento_id in ('FA','BO','TK') group by cid_transportista
>) OrdersSum ON b.cID_Transportisa = OrdersSum.cID_Transportisa
>  where ctipo_proveedor_id='05'
>and cestado_prov_id='A'
>and a.cproveedor_id=b.cproveedor_id
>
>
>From the vechicles query we LEFT JOIN to the Orders summary.
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform