SELECT *
FROM ct_factura,ct_dosificacion,ct_contrato_cuenta
WHERE ct_contrato_cuenta.n_id_factura = ct_factura.n_id
and ct_dosificacion.n_id = ct_factura.n_id_dosificacion
I have the following query, where ct_factura and ct_dosificación return a single record, however the join between ct_factura and ct_contrato_cuenta, returns more than one record, as I do so that they only do the join and return a single record between these two tables: ct_factura and ct_contrato_cuenta, thanks in advance
Verify your data, I send you an example of a group by.
if OBJECT_ID('tempdb..#temp1') is not null drop table #temp1;
if OBJECT_ID('tempdb..#temp2') is not null drop table #temp2;
create table #temp1 (a int);
create table #temp2 (b int, c int);
insert #temp1 values(1);
insert #temp2 values(1,1);
insert #temp2 values(1,2);
--- en la tabla #temp2 se encuentran 2 registros en 1 por lo cual se duplica la información,
--- por lo cual si quieres solo un registro tienes que trabajar el registro que es diferente en este caso se suma
select *
from #temp1 a
inner join #temp2 b
on a.a = b.b;
select a, sum(c) suma
from #temp1 a
inner join #temp2 b
on a.a = b.b
group by a;
Dear, I was able to resolve the query:
SELECT ct_medicion_grupo.v_nombre,count(*)
FROM ct_factura
inner join (select distinct cuenta.n_id_factura id_fac, cuenta.n_id_contrato id_con
from ct_contrato_cuenta cuenta
inner join ct_factura on ct_factura.n_id = cuenta.n_id_factura) contrato_cuenta on contrato_cuenta.id_fac = ct_factura.n_id
inner join ct_contrato on ct_contrato.n_id = contrato_cuenta.id_con
inner join ct_propiedad on ct_propiedad.n_id = ct_contrato.n_id_propiedad
inner join ct_propiedad_division_geo on ct_propiedad_division_geo.n_id_propiedad = ct_propiedad.n_id
inner join ct_division_geo on ct_division_geo.n_id = ct_propiedad_division_geo.n_id_division_geo
inner join ct_medicion_grupo_division_geo on ct_medicion_grupo_division_geo.n_id_division_geo = ct_division_geo.n_id
inner join ct_medicion_grupo on ct_medicion_grupo.n_id = ct_medicion_grupo_division_geo.n_id_medicion_grupo
and ct_factura.n_id IN (
select ct_factura.n_id
from ct_lectura
inner join ct_ruta_asignacion on ct_ruta_asignacion.n_id = ct_lectura.N_ID_RUTA_ASIGNACION
left join ct_factura on ct_factura.V_PERIODO = to_char(to_date(ct_lectura.N_MES||'/'||ct_lectura.N_ANIO,'mm/yyyy'),'MON/yyyy','NLS_DATE_LANGUAGE = SPANISH')
and ct_lectura.N_CUENTA = ct_factura.v_cuenta
and ct_factura.C_CODIGO_FACTURA_TIPO = 'MES'
where ct_ruta_asignacion.n_id_cronograma_medicion_grupo in(5456,5470,5458,5459,5460,5461,5462,5463,5464,5465,5466,5467,5468,5469,5457)
and (ct_factura.n_id is not null or ct_lectura.c_estado = 7))
group by ct_medicion_grupo.v_nombre;
Thanks anyway for all the help.