MS SQL Server MSEQ - Records Duplicating after a Join

Author

Records Duplicating after a Join

Wez

2005-06-17, 1:24 pm

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

2005-06-17, 8:24 pm

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)

Sponsored Links