SQL query help, please

Question

Hi there.

I need help with building SQL query.

I have 4 tables: Sellers, Goods, Projects and Sales.

Sellers table has following structure:

SellerID (int) PK
SellerName (nvarchar)
SellerStatus (int)
SellerCity (nvarchar)

Goods:

GoodsID (int) PK
GoodsTitle (nvarchar)
GoodsColor (nvarchar)
GoodsSize (int)
GoodsCity (nvarchar)

Projects:

ProjectID (int) PK
ProjectTitle (nvarchar)
ProjectCity (nvarchar)

Sales:

SellerID (int)
GoodsID (int)
ProjectID (int)
Price (int)

I need to get Sellers ID's, which distribute same Goods to all Projects.

Can anybody help me with query? I use MSSQL.

Thanks in advance

Answer 1

SELECT * FROM Sellers as se INNER JOIN Sales as sa on sa.SellerID = se.SellerID INNER JOIN Goods as g on g.GoodsID = sa.GoodsID INNER JOIN Projects as p on p.ProjectID = sa.ProjectID WHERE se.SellerID = 6

this will select everything from user 6, you can limit it even more if you want

Answer 2

I honestly aint sure if i have understood exactly all the requirements (i did go through all the comments but am a bit confused after that). However, if you want ALL Sellers who have SOLD atleast 1 GOODs to ALL projects, then the below might give you that, i think.

Can you try it out and see if it does? I really have no access to a DB to try it out right now

Also, in case it isnt meeting some requirement which i have missed, please feel free to elaborate a bit, maybe using example data - which might make it simpler for all.

SELECT SellerID FROM SALES
GROUP BY SellerID, GoodsID
HAVING COUNT(ProjectID) = SELECT count(ProjectID) FROM Projects

Answer 3

Try this, let me know if it's the right direction:

select SellerID
from   Sales s1 join Goods g1 on (GoodsID)
where  not exists 
    (select SellerID, GoodID
     from Sales s2
        join Goods g2 on (GoodsID)
        left join Sales on (SellerID, GoodsID)
     where s1.Seller = s2.Seller and 
           g1.GoodsID = g2.GoodsID and
           Sales.ProjectID is null)