Re: Records Duplicating after a Join

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

Relevant Pages

  • Records Duplicating after a Join
    ... Orders (ORDERID, CODE, ORDERQTY) ... 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 ...
    (microsoft.public.sqlserver.mseq)
  • Re: OT - Walmart Service Surprise
    ... They had a computer on display ... that they weren't going to stock any more and they had none in stock, ... but they wouldn't sell the floor model because that is their policy. ... but that he would ask a manager. ...
    (rec.crafts.textiles.quilting)
  • Re: Question on displaying repeating data
    ... "Rich" wrote in message ... > enter/display opening and closing stock prices for a given company. ... The query logic is relatively simple. ... > My problem is that I don't know how to display this. ...
    (microsoft.public.access.forms)
  • Re: Give user ability to copy last record?
    ... The problem is, if the OrderID is an autonumber, how do you determine the ... the form will display the first record in the ... ShipperID, CustPOnum, InvoiceNo, OrderDate, RequiredDate, ShippedDate, ... Set rsobj = CurrentDb.OpenRecordset'target table ...
    (microsoft.public.access.modulesdaovba)
  • Re: Need simple models of Electronic Gadgets...
    ... > I design displays for the retail industry and have need client asking ... > to show some stock on a display I've created. ...
    (comp.cad.solidworks)