SQL + Left Join

Question

SQL + Left Join

Asked 7 years, 11 months ago

Viewed 87 times

-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 were not sold, that is, from always 2016+2015+....etc., but only wanted the 2015 ones.

What exactly is the type of your date field in the table?

– Kenny Rafael

2016/12/10 at 12:46
Great, the guy’s date

– Dexter

2016/12/10 at 13:49
@Dexter which database you are using?

– Sorack

2016/12/12 at 11:10

2 answers

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.

by Sorack • **27,430** points · Answer 1 · 2016-12-10T12:06:19+00:00

2

In reality 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)

Observing: as the year is set 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.

In free translation:

The EXISTS SQL condition is used in conjunction with a subquery and is deemed to be met if the subquery returns at least one line.

The problem is that the date is in the sale table, so this solution does not work. Thanks

– Dexter

2016/12/10 at 12:59
@Dexter, you have to agree that it doesn’t make sense for you to get information from the sales table when you’re looking for something that shouldn’t be there

– Sorack

2016/12/10 at 13:01
I have the following logic, Product-i dProduct int not null Identity(1,1), Description varchar(120) not null, idCategoria tinyint not null, precoUnitario decimal(6,2) not null, promocao int null, Cartprod got id cart and idProd and quantity. then on sale have id sale,idFunctioning, idCarrinho, dating etc

– Dexter

2016/12/10 at 13:04
Particularly I don’t like subquery solutions, regardless of how they work, they should be avoided for performance reasons.

– Kenny Rafael

2016/12/10 at 13:58
@Kennyrafael If misused affect performance even. In this case, any solution without subquery will make the outcome dependent on a sale. If you try a year that there is no sale no result will appear since you have an Internet with sale and this is completely incorrect. I suggest you read the answer to that question: Can subqueries decrease performance? Myth or truth?

– Sorack

2016/12/10 at 15:56
I can only think of a solution for NOT EXISTS or NOT IN and there are researches where the result is more important than efficiency , it will run once and that’s it.

– Motta

2016/12/10 at 21:39
@Sorack, that’s what the left join.

– Kenny Rafael

2016/12/12 at 10:12
@Kennyrafael O LEFT JOINserves to bring information to the left even if there is no right. In this case, it should not depend on sales. The solution with LEFT in this case is flawed as it presents the problem I explained in my last comment.

– Sorack

2016/12/12 at 10:46
@Sorack, the left join is used perfectly in this case precisely for this reason, filtering the ano as I did in my reply, just add one more where where the result of the right just doesn’t exist, since he needs to know what he DIDN’T sell that year...

– Kenny Rafael

2016/12/12 at 10:57
@Kennyrafael you make a FROM on the table vendas, will never bring any result in a year that has not yet had sale.

– Sorack

2016/12/12 at 10:59
1

@Sorack, thank you very much, despite your misunderstanding regarding the use of LEFT JOIN On this issue, you made me realize that you were dealing wrong with @Dexter’s business rule, anyway, I think you got too hung up on that (business rule), and you didn’t understand what the feature could offer. I hope you understand how a constructive criticism, because both solutions work, there is no need to kill yourself for it.

– Kenny Rafael

2016/12/12 at 11:30
@Kennyrafael quiet, I also have no intention of offending you, all I said was as constructive criticism also and no intention of attacking in any way

– Sorack

2016/12/12 at 12:07

Show 7 more comments

by Kenny Rafael • **6,698** points · Answer 2 · 2016-12-10T13:57:19+00:00

If your field is one date, just 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 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, the ideal is that your main table(FROM), be it Produto instead of Venda, which consequently changes the order of JOINs of your inquiry, after that the first condition (in both queries) guarantees you will not bring products sold in the year of filtration and the second condition ensures that you will also bring products that have never been sold. I also removed the p.descricao of group by, since you are already grouping by p.idProduto.