SQL nested SELECT with JOIN

Question

I wasted all the day on one query without success , SOS I need a help :) with a given @CustomerId , I need to query all the Products that linked to customer seller can sell but not sold to him before , the Commissions table is indication of what products seller can sell

enter image description here

Thanks in advance

How do you know which products a seller can sell? By the commissions table perhaps? — Mureinik
– Mureinik, Commented Sep 8, 2014 at 16:33
@DimaRudaev: You are connecting customer and product through sales table? how you determine whether product been sold or not? — huMpty duMpty
– huMpty duMpty, Commented Sep 8, 2014 at 16:35
a "Products" table name is bad choice , you can consider it like "Categories" — Chipopo
– Chipopo, Commented Sep 8, 2014 at 16:38
each record in Sales table indicates a sale , customer can purchase only 1 of each products — Chipopo
– Chipopo, Commented Sep 8, 2014 at 16:43

The Conspiracy · Accepted Answer · 2014-09-08 17:12:30Z

2

SELECT sellableProduct
FROM (SELECT Comissions.ProductId AS sellableProduct, Sellers.SellerId AS sellableSeller FROM Comissions INNER JOIN Sellers ON Comissions.SellerId=Sellers.SellerId INNER JOIN Customers ON Sellers.SellerId=Customers.SellerId WHERE Customers.CustomerId = @customerid) AS tblSellable
LEFT JOIN (SELECT Sales.ProductId AS soldProduct, Customers.SellerId as soldSeller FROM Customers INNER JOIN Sales ON Customers.CustomerId=Sales.CustomerId WHERE Customers.CustomerId = @customerid) AS tblSold
ON tblSellable.sellableProduct=tblSold.soldProduct AND tblSellable.sellableSeller=tblSold.soldSeller
WHERE tblSold.soldProduct IS NULL

this way you avoid time-consuming IN statements

If a Customer can only have one Seller, then you can omit the seller link:

SELECT sellableProduct
FROM (SELECT Comissions.ProductId AS sellableProduct FROM Comissions INNER JOIN Sellers ON Comissions.SellerId=Sellers.SellerId INNER JOIN Customers ON Sellers.SellerId=Customers.SellerId WHERE Customers.CustomerId = @customerid) AS tblSellable
LEFT JOIN (SELECT Sales.ProductId AS soldProduct FROM Sales WHERE Sales.CustomerId = @customerid) AS tblSold
ON tblSellable.sellableProduct=tblSold.soldProduct
WHERE tblSold.soldProduct IS NULL

edited Sep 8, 2014 at 17:12

answered Sep 8, 2014 at 16:54

The Conspiracy

4,0431 gold badge20 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

The Conspiracy Over a year ago

I edited the solution, nevertheless you're welcome to find the word "customers" yourself...

Chipopo Over a year ago

It works , thank you very much i really appreciated it , it is very complicated query !!! You are a SQL-NINJA - RESPECT

Chipopo Over a year ago

i need to enhance it a little bit , your query return ProductId's i want to JOIN them with Products.Name and Products.Description , does it possible somehow to add a "JOIN Products ON ProductId" to this query ?

The Conspiracy Over a year ago

yes that's possible. In FROM (SELECT add the additional columns and the inner join. Also add the columns in the SELECT at the beginning.

Mureinik · Accepted Answer · 2014-09-08 17:08:16Z

1

Basically, you're looking for products that have a record in commissions, but not in sales. Using :id to denote the specific ID:

SELECT *
FROM   products
WHERE  productid IN (SELECT productid
                     FROM   commissions
                     WHERE  sellerid = :id) AND
       productid NOT IN (SELECT productid
                         FROM   sales
                         JOIN   customers ON sales.customerid = cusomers.customerid
                         WHERE  sellerid = :id)

edited Sep 8, 2014 at 17:08

answered Sep 8, 2014 at 16:38

Mureinik

316k54 gold badges400 silver badges405 bronze badges

5 Comments

Chipopo Over a year ago

you mean that i need to query associated SellerId to given customer and then run your query with :id = SellerId ?

Chipopo Over a year ago

"sales.sellerid" there is no sellerid field in sales Table

Mureinik Over a year ago

arg, wasn't paying attention. Fixed.

Chipopo Over a year ago

let me understand , you tell me to query it by SellerId that i can query it from Customer table with @CustomerId ??

Chipopo Over a year ago

your query answers to question "which products a seller can sell that he didn't sold before" and i need an answer to question "which products a seller can sell that he didn't sold before to specific customer @CustomerId" , thanks anyway

SeraphimFoA · Accepted Answer · 2014-09-08 16:38:08Z

0

Would this work?

SELECT sell.*, prod.* FROM 
Sellers sell
INNER JOIN Customers cust ON cust.SellerId = sell.SellerId
LEFT JOIN Commissions comm ON sell.SellerId = comm.SellerId
LEFT JOIN Products prod ON prod.ProductId = comm.ProductId
WHERE prod.ProductId NOT IN (
SELECT ProductId
FROM Products p INNER JOIN 
Sales s ON s.ProductId = p.ProductId
WHERE s.CustomerId = @CustomerId

It get all the sellers and the respective product from commission, where the product Id is NOT associated with any sale of the client

answered Sep 8, 2014 at 16:38

SeraphimFoA

4764 silver badges11 bronze badges

1 Comment

Chipopo Over a year ago

syntax error - Line 7 Ambiguous column name 'ProductId', also only 1 seller associated with each customer

Collectives™ on Stack Overflow

SQL nested SELECT with JOIN

3 Answers 3

4 Comments

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related