Query de ventas

[former_member211460]

Hola Foro

tengo una duda respecto a un query de ventas cuando lo ejecuto asi si funciona bien y me arroja los datos, es este

SELECT T0.DocDate AS Fecha, T0.DocNum AS Docto, T0.CardName AS Cliente, T0.NumAtCard AS Pedido,

T1.ItemCode AS Referencia, T1.Dscription AS Articulo, T1.Quantity AS Cantidad, T1.Price AS Precio, T1.Quantity * T1.Price AS Total

FROM OINV AS T0 INNER JOIN

INV1 AS T1 ON T0.DocEntry = T1.DocEntry

WHERE (T0.CardName = N'SEPRT DE C.V.') AND (T0.DocType = 'I')

Pero cuando le agrego otra tabla ya no me arroja informacion

SELECT T0.DocDate AS Fecha, T0.DocNum AS Docto, T0.CardName AS Cliente, T0.NumAtCard AS Pedido,

T1.ItemCode AS Referencia, T1.Dscription AS Articulo, T1.Quantity AS Cantidad, T1.Price AS Precio, T1.Quantity * T1.Price AS Total, T2.County

FROM OINV AS T0 INNER JOIN

INV1 AS T1 ON T0.DocEntry = T1.DocEntry INNER JOIN

CRD1 AS T2 ON T0.Address = T2.Address

WHERE (T0.CardName = N'SEPRT DE C.V.') AND (T0.DocType = 'I')

aqui lo unico que hago es agregar la columna T2.County de la tabla CRD1 lo hacemos asi porque el nombre con el que identificamos la tienda a la que se factura se lo ponemos ahi

Gracias por su ayuda

Saludos

[former_member188440]

No hay join entre la oinv.address y la crd1.address

ya intentaste un left join? o algo similar¡

[former_member211460]

Gracias por sus respuestas

ya lo intente tambien asi , de hecho aplique el query de Felipe pero la unica columna que no me arroja datos sigue siendo la de County

saludos

[former_member188440]

Intenta asi

SELECT T0.DocDate AS Fecha, T0.DocNum AS Docto, T0.CardName AS Cliente, T0.NumAtCard AS Pedido,
T1.ItemCode AS Referencia, T1.Dscription AS Articulo, T1.Quantity AS Cantidad, T1.Price AS Precio, T1.Quantity * T1.Price AS Total, T2.County
FROM OINV AS T0 INNER JOIN
INV1 AS T1 ON T0.DocEntry = T1.DocEntry 
left join

CRD1 AS T2 ON T0.PayToCode   = T2.Address AND T0.CardCode =T2.CardCode 

where .............

El paytocode es para direccion de Factura, si requieres el County de direccion de entrega cambia por ShiptoCode

[felipe_loyolarodriguez]

Utiliza un LEFT JOIN

SELECT T0.DocDate AS Fecha, T0.DocNum AS Docto, T0.CardName AS Cliente, T0.NumAtCard AS Pedido,
T1.ItemCode AS Referencia, T1.Dscription AS Articulo, T1.Quantity AS Cantidad, T1.Price AS Precio, T1.Quantity * T1.Price AS Total, T2.County
FROM OINV AS T0 INNER JOIN
INV1 AS T1 ON T0.DocEntry = T1.DocEntry LEFT JOIN
CRD1 AS T2 ON T0.Address = T2.Address
WHERE (T0.CardName = N'SEPRT DE C.V.') AND (T0.DocType = 'I')

Atte.