wezgv&r

I have two tables as follows:

Orders (ORDERID, CODE, ORDERQTY)
and
STOCK (CODE, LOCATION, STOCKQTY).

I am running a query that will display all the records in the Orders table,
and also a valid location for that code i.e. Fields returned are:

ORDERID, CODE, ORDERQTY, LOCATION

I am using an INNER JOIN to get the result.

This is fine if there is only one location per code but if I have multiple
locations for a code then the result set will show all locations (therefore
duplicating rows).

How do I just display one location code for each record in the orders table?
I dont mind which location code it is - I just need to avoid having multiple
records returned!

Thanks,
Wez

hugo kornelis

Hi Wez,

In the future, please include table structure (as CREATE TABLE
statements), some sample data (as INSERT statements) and expected output
when asking for help - it makes helping you a lot easier if I don't have
to guess! See http://www.aspfaq.com/5006 for more details.

Anyway, try if this helps:

SELECT o.OrderId, o.Code, o.OrderQty,
MIN(s.Location) AS Location
FROM Orders AS o
INNER JOIN Stock AS s
ON s.Code = o.Code
GROUP BY o.OrderId, o.Code, o.OrderQty


Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)