Records Duplicating after a Join

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
On Fri, 17 Jun 2005 10:07:07 -0700, Wez wrote:

>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!
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 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)