Project Detail

Description

I need a stored procedure (T-SQL, MSSQL 2005) that returns a list of availible addresses from a database.

buy_orders_pallets (table 1) (contains what clients want to buy)
bid int <- passed as parameter to SP
pid int
amount int
isdry bit

customer_avi (table 2) (contains what clients want to sell)
pid int <- pallet id
aid int <- address id (This is what I want returned)
amount int
isdry bit


Here is the two tables, I want a SP that takes 1 parameter (bid) as input. use this bid to get rows from table 1
Then return all address id (aid) from table 2 where
ALL of the following criteria is met.
1. all pid from table 1 is inside table 2 (matching the aid)
2. amount is equal or greater in table 2 than in table 1
3. isdry is equal


Example data below would on input bid= 1 return values 1 and 4

Table 1
bid pid amount isdry
1 1 5 true
1 2 7 false
1 5 2 true

Table 2
pid amount isdry aid
1 10 true 1
2 7 false 1
5 2 true 1

1 150 true 2
5 20 true 2

1 110 true 3
2 70 true 3
5 21 true 3

1 15 true 4
2 75 false 4
5 21 true 4