CODEWITHSUNDEEP
sqldatabaseoracle-database
Patryk
Patryk

Reputation: 3

Select from table where condition in another table

I have four tables:

SELLER (ID, NAME)
CLIENT (ID, NAME)
SELLER_CLIENT (SELLER_ID, CLIENT_ID)
CLIENT_SOLD (CLIENT_ID, VALUE, DATE)

I need to write query to pick all Sellers which clients bought more that 500 (value) in selected date. Can anyone give me a little tip how can I do it or what to use?

My basic knowledge is that I need to use 

SELECT * FROM SELLER WHERE...

EDIT EXAMPLE SELLER TABLE

ID | NAME
1 | s1
2 | s2
3 | s3
4 | s4
5 | s5
6 | s6

CLIENT TABLE

ID | NAME
1 | k1
2 | k2
3 | k3
4 | k4
5 | k5
6 | k6

CLIENT_SELLER TABLE

CLIENT_ID | SELLER_ID
1         | 2
2         | 3
3         | 3
4         | 1
5         | 6
6         | 6

CLIENT_SOLD

CLIENT_ID | VALUE | DATE
1 | 600 | 13.04.2015
2 | 360 | 22.02.2015
3 | 501 | 11.06.2015
4 | 800 | 09.01.2015
5 | 112 | 12.02.2015
6 | 98 | 08.02.2015

And all time query should return me sellers with id 2, 3, 1

Upvotes: 0

Views: 3275

Answers (2)

Jibin Balachandran
Jibin Balachandran

Reputation: 3441

Try this:

SELECT s.id,s.name FROM CLIENT_SOLD cs
INNER JOIN SELLER_CLIENT sc 
ON cs.CLIENT_ID=sc.CLIENT_ID
INNER JOIN SELLER s
ON s.ID=sc.SELLER_ID 
WHERE cs.VALUE>500
AND cs.DATE=@DATE

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460038

A simple correlated subquery to sum the value should do the job:

SELECT s.* FROM SELLER s 
WHERE ( SELECT SUM(Value) 
        FROM CLIENT_SOLD cs 
        WHERE cs.SELLER_ID = s.ID 
          AND cs.DATE = @date) > 500

Upvotes: 2

Related Questions