[SOLVED] SQL question - Urgent!

[scorplev]

Hi you guyz, someone has recommended this forum. I'll be happy if you can help me solve a method to approach the following problem. please see attached txt file with problem details...

 

please this is urgent!

 

[attachment deleted by admin]

[trq]

Its generally considered better to actually post your question in a forum, allot of people wont even download files.

[onedumbcoder]

WARNING I JUST GOT A VIRUS ALERT DOWNLOADING THAT!!!! MODS DELETE THAT FILE ASAP!!!!!

 

Edit (Daniel0): Removed annoying formatting.

[scorplev]

Are you kidding me?

no virus in a simple txt file, or does my fact being jewish caused you report this file as a virus.

Moderator, I highly reccomend you to check the file yourself and ban this racist user.

 

any way, this is the content of the file:

tables structure:

 

Supplier(s_no, s_name, status, s_address, s_city)

Project(p_no, p_name, p_manager, p_address, p_city)

Material(m_no, m_name, unit, unit_price)

Order(o_no, p_no, s_no, m_no, units, o_date)

 

where:

 

s_no: supplier number

p_city: project city

m_no: material number

unit: unit of mesure (e.g.: kg, gr, etc)

unit_price: price for that material

 

I need to find a method to find the following:

- find all the suppliers that have supplied at least 10 orders to projects taken place in a specific city, for each supplier i need to find the total amount of money he've ordered in the past (for all projects, not only the ones taken place before).

[Daniel0]

Moderator, I highly reccomend you to [...] ban this racist user.

 

Done.

[kickstart]

Hi

 

Something like this. However I have assumed you are want suppliers who have 10 or more projects in the specified city, but you want the total of their orders irrespective of cities.

 

SELECT a.s_no, SUM(OrderMaterialPrice) 
FROM (SELECT supplierNo
FROM (SELECT a.s_no AS supplierNo, COUNT(*) AS ProjCount
FROM Supplier a
INNER JOIN Order b
ON a.s_no = b.s_no
INNER JOIN Project c
ON b.p_no = c.p_no
WHERE p_city = '$SomeCity'
GROUP BY a.s_no) Deriv1
WHERE ProjCount >= 10) Deriv3
INNER JOIN Order ON Deriv3.SupplierNo = Order.s_no
INNER JOIN (SELECT OrderNo, SUM(MaterialPrice) AS OrderMaterialPrice
FROM (SELECT o.o_no AS OrderNo, m_no, (o.units * m.unit_price) AS MaterialPrice FROM Order o INNER JOIN Material m ON o.m_no = m.m_no GROUP BY o.o_no , m_no) Deriv2
GROUP BY OrderNo) Deriv4
ON Order.o_no = Deriv4.OrderNo
GROUP BY a.s_no

 

Probably a few typos but using SELECTs in this way is the only way I can think to do it at the moment.

 

All the best

 

Keith

[scorplev]

Thank you very much!!! that's exactly what i've been looking!!!