SQL + Left Join

Question

SQL + Left Join

Navigation

#1 by (2 votes)
#2 by (2 votes)

-1

I have 3 tables produto , carrinhoProduto and venda I wanted to know the products that did not sell in the year 2015 :

SELECT p.idProduto,p.descricao as 'Produto',datepart(year,v.dataVenda) as 'Ano'
FROM Venda v
INNER JOIN CarrinhoProduto cp ON v.idCarrinho = cp.idCarrinho
LEFT JOIN Produto p ON p.idProduto = cp.idProduto
WHERE cp.idProduto IS NULL AND datepart(year,GETDATE()) = 2015
GROUP BY p.idProduto, p.descricao, v.dataVenda

The result is all the products that did not sell, that is, always 2016 + 2015 + etc., but only wanted those of 2015.

sql

asked by anonymous 10.12.2016 / 03:35

2 answers

Comparison between decimal numbers Get comma-separated values from the database and organize them in li

score 2 · Answer 1

If your field is a date , simply filter with a simple operator:

SELECT p.idProduto, p.descricao as 'Produto', YEAR(v.dataVenda) as 'Ano'
FROM Produto p
LEFT JOIN Venda v ON p.idProduto = cp.idProduto
LEFT JOIN CarrinhoProduto cp ON v.idCarrinho = cp.idCarrinho
WHERE YEAR(v.dataVenda) <> 2015 OR v.dataVenda IS NULL
GROUP BY p.idProduto, v.dataVenda

Or even using a BETWEEN :

SELECT p.idProduto, p.descricao as 'Produto', YEAR(v.dataVenda) as 'Ano'
FROM Produto p
LEFT JOIN Venda v ON p.idProduto = cp.idProduto
LEFT JOIN CarrinhoProduto cp ON v.idCarrinho = cp.idCarrinho
WHERE YEAR(v.dataVenda) NOT BETWEEN 2015 AND 2015 OR v.dataVenda IS NULL
GROUP BY p.idProduto, v.dataVenda

In this case, ideally your main table ( FROM ) is Produto instead of Venda , which consequently changes the order of the JOIN s of your query, after that the first condition (in both queries) ensures you will not bring products sold in the year of the filtering and the second condition guarantees that you will also bring products that have never been sold. I also removed p.descricao from group by , since you are already grouping by p.idProduto .

score 2 · Answer 2

Actually the best way would be with NOT EXISTS since the clause exists exactly for this purpose:

SELECT p.idProduto,
       p.descricao AS 'Produto',
       2015 AS 'Ano'
  FROM Produto p
 WHERE NOT EXISTS(SELECT
                    FROM Venda v
                         INNER JOIN CarrinhoProduto cp ON v.idCarrinho = cp.idCarrinho
                   WHERE cp.idProduto = p.idProduto
                     AND DATEPART(YEAR, v.dataVenda) = 2015)

Note: As the year is defined in a variable, it is probably not interesting to return it in this query .

EXISTS
The SQL EXISTS condition is used in combination with a subquery and is considered to be met, if the subquery returns at least one row.

Free translation:

The SQL EXISTS condition is used in conjunction with a subquery and is considered met if the subquery returns at least one row.