Fairly advanced MS SQL query

fluiduk

fluiduk

fluiduk

Associate
Joined
13 Nov 2003
Posts
1,567
Location
Manchester
Hi All

I need some help with an sql query for MS SQL.

Basically we have various items that come from multiple sources, ie our own in house stuff, and that from external suppliers. In some instances one product is on both sources. At the moment I have a 'consolidation' script which imports all of the instances into a 'master list'.

What I need now is an sql query that finds all ocurrences of a particular product in a list ( using our product code) and then works out the total stock holding if there is more than 1 source.

Im not sure if this is possible or the best way to do it.

As usual any help is appreciated.

Thanks
Aaron
 
Hi Arty

Thanks for the reply

There are 2 main tables that are used for this.

The first is the 'master_list' table.

This contains all the product cost and stock information.
It has the following fields.

manf_pn -Manufacturer Part Number
manf - Manufacturer
cost
stock
weight
source - this is a two character representation of the source of the item

In this table are all of the products from all of our suppliers, so a lot are duplicated. However the cost and stock figures may be different. To combat this, we use the lowest price, but we want to combine the total amount of stock from each source as our stock holding.

We then have a product_data table in which all of our product data, spec, image etc is held.

This table is linked to the 'master_list' by the manf_pn

Hope that makes sense lol
Aaron
 
Is that enough info for now?

Also, please feel free to MSN me if that is more convenient

Thanks In Advance
Aaron
 
Hi Beansprout

I think thats the way we will have to go, as the db is getting huge.

However, in the mean time a query from a guru would be great ;)

Aaron
 
Maybe it just pretty advanced for me lol :p

What I need the query to do is find all records with a particular manf_pn and add together all the stock for those records into 1 value, which I can then display on the page.

Thanks
Aaron
 
Hi Lloydsj

I tried the query you suggested which gave me this error.

'Column masterlist.manf_pn is invalid in the select list because it is not contained in either an aggregate function or the group by clause'

Sadly I have no idea what that means lol, any help is greatly appreciated

Many Thanks
Aaron
 
You must log in or register to reply here.
Back
Top Bottom