query between 5 Sql Server tables (some without direct relation)

Question

query between 5 Sql Server tables (some without direct relation)

Navigation

#1 by (0 votes)

0

I need to complete this query (sql server 2008):

select EstadoCotizacion.var_eco_descripcion, id_cot, 
       Usuario.var_usr_nombres, fec_cot_emision, fec_cot_aceptacion 
       from COTIZACION 
       inner join USUARIO 
          on COTIZACION.id_eje=usuario.id_USR 
       inner join estadocotizacion 
          on COTIZACION.id_eco=ESTADOCOTIZACION.id_eco

to show something like this:

var_eco_descripcion - id_cot - var_ser_descripcion - (var_usr_nombres & var_usr_appaterno & var_usr_apmaterno) - suma(valor_unitario_item) - fec_cot_emision - fec_cot_aceptacion

I have the following tables

dbo.cotizacion

[id_cot]
[id_eje] -----> es igual al campo [id_usr] de dbo.usuario
[fec_cot_emision]
[fec_cot_aceptacion]
[var_cot_observacion]
[id_cli]
[id_eco]

dbo.estadocotizacion

[id_eco]
[var_eco_descripcion]

dbo.user

[id_usr]
[var_usr_login]
[var_usr_password]
[var_usr_nombres]
[var_usr_appaterno]
[var_usr_apmaterno]

dbo.services

[id_ser]
[id_ane]
[var_ser_descripcion]

dbo.detallecotizacion

[id_dco]
[id_cot]
[id_ser]

dbo.ItemDetalleCotizacion

[Id_Item]
[id_dco]
[Descripción_Item]
[Cantidad_Item]
[Valor_Unitario_Item]

sql-server

asked by Cris 13.08.2017 в 00:07

source

1 answer

How to receive a Variable Type DateTime from MYSQL in PHP file How to perform Querys that show a data of the row of the related foreign key?

score 0 · Answer 1

To link the remaining tables that are missing, you must only specify the relationship on the JOIN ON with which the tables are related. In this case only you know the related fields, either by Foreign Key (FK) or not. If you do not know what they are, check the data in the tables that are missing and then link them.

You must also take into account the different JOINs you can use, left, right, inner, full outer join. Depending on the related data. In any case, start with INNER JOIN, to only obtain the data that exists in all the related tables.

Something else, in your structure I notice something in the tables dbo.cotizacion, dbo.detallecotizacion and dbo.itemDetalleCotizacion :

dbo.cotizacion: seems to have as PK, id_cot.
dbo.detalleCotizacion: it seems to have a compound PK with id_cot and id_dco .
dbo.ItemDetalleCotizacion: it seems to have a composite PK with Id_Item and id_dco , but where was id_cot ??.

I recommend that you review the data and create a diagram of your tables and then create the sentence that will help you. Greetings.