I have 2 tables.
The output I need is:
- Supplier_id
- supplier_name
- Grade_id
- grade_name
- customer_name
- customer_id
Query I am using is:
select distinct gs.Supplier_id,s.supplier_name,gs.Grade_id,g.grade_name,
c.customer_name,c.customer_id
from tbl_Customer c, tbl_GradeSupplier gs
left join tbl_Suppliers s on s.supplier_ID = gs.Supplier_id
left join tbl_Grades g on g.grade_id = gs.Grade_id
However now i need to add to the return of this query the highest date valid from which is before the current date for each one of these as well as the old prices for that date This information can be found on the Prices table which holds
price_id grade_id supplier_id price_purchase_value price_validfromdate customer_id price_sell_value price_deleted
41 8 24 2190.00 2012-09-09 00:00:00.000 4 2280.00 NULL
I tried to do this:
select distinct gs.Supplier_id,s.supplier_name,gs.Grade_id,g.grade_name,
c.customer_name,c.customer_id
from tbl_Customer c, tbl_GradeSupplier gs
left join tbl_Suppliers s on s.supplier_ID = gs.Supplier_id
left join tbl_Grades g on g.grade_id = gs.Grade_id
inner join
(select grade_id,customer_id,max(price_validfromdate) as validfrom
from tbl_Prices a
where price_validfromdate <= GETDATE()
group by supplier_id,grade_id,customer_id) prices
on prices.customer_id = c.customer_id and prices.grade_id = gs.Grade_id
which gives me this error: The multi-part identifier "c.customer_id" could not be bound.
Any ideas?
