Obtain a list of Products with a relation of 4 SQL Server tables

Question

Obtain a list of Products with a relation of 4 SQL Server tables

Navigation

#1 by (0 votes)

0

I'm trying to get a list of products sold in the last month, with a certain feature, which in this case would be your classification code. The question is that I have 4 related tables, Products, ClassificationProducts, DetailVentas, EncavesadoVentas.

Now, this I have done only with three tables that would be like this:

SELECT DISTINCT( p.CodPro) AS 'Code',p.NomPro AS 'Product Name',
CONVERT(DECIMAL(10,0),p.PreVta) AS 'Price'
FROM Productos p INNER JOIN DetVtas d
ON p.CodPro = d.CodPro
WHERE p.CodCla IN (01,02,05)
AND p.Desactivado = 0
ORDER BY p.CodPro ASC;

As you can see, this gives me a list of the products sold, which I can find in details of sales with certain classification that I want.

Now, I want to do the same but with a certain length of date, obtaining a list of the month that I want, and in this case to relate the table EncVtas (Head Sales) where the date of the invoice is recorded with the table DetVtas ( Sales detail) which is the one that records the details of the invoice.

This is the example diagram, I do not enter all the fields because the query would be very big.

Someone if you can guide me please, where the thing is going.

Greetings people.

sql

asked by masonhsp 31.12.2018 в 21:05

source

1 answer

I do not see the sprite of the parent class ServerSocket and Socket java

score 0 · Accepted Answer

The field that relates the table DetVtas with the table EncVentas is NumFactura, for that reason your INNER JOIN must do it relating these two fields. On the other hand to select a specific month you can use the MONTH and YEAR functions, the query would look like this:

SELECT DISTINCT( p.CodPro) AS 'Code',p.NomPro AS 'Product Name',
CONVERT(DECIMAL(10,0),p.PreVta) AS 'Price'  
FROM Productos p 
INNER JOIN DetVtas d ON p.CodPro = d.CodPro
INNER JOIN EncVtas e ON e.NumFactura = d.NumFactura
WHERE 
p.CodCla IN (01,02,05)
AND 
p.Desactivado = 0
AND
(
MONNTH(e.FecFactura) = 1 -- El número del mes siendo entre 1 (Enero) y hasta 12  (Diciembre)
AND
YEAR(e.FecFactura) = 2018 -- El año que deseas filtrar
)
ORDER BY p.CodPro ASC;