Return first value on linked table

[treva26]

I have a QUOTES table and an ITEMS table
There are multiple rows in the ITEMS table for each quote linked by the QuoteDBID field.

Can I write a query that will return one row for each QUOTE with a column containing only the first value for that quote.

I have this, but it returns a row for each ITEM

SELECT Quotes.QuoteDBID, Quotes.[Quote Num], Items.[Items ID], Items.Description
FROM Quotes INNER JOIN Items ON Quotes.QuoteDBID = Items.QuoteDBID
ORDER BY Quotes.[Quote Num];

 

[georgedwilkinson]

Try something like:

SELECT 
Quotes.QuoteDBID, 
Quotes.[Quote Num], 
Items.[Items ID], 
Items.Description
FROM 
Quotes 
INNER JOIN 
(select top 1 Items.QuoteDBID, Items.[Items ID], Items.Description from Items) as Items 
ON Quotes.QuoteDBID = Items.QuoteDBID
ORDER BY Quotes.[Quote Num];

Please note this is air code and is used only to demonstrate one of the many ways (but not necessarily the best way) to accomplish this. I haven't tested it and I leave that totally up to you if you choose to use it.

 

[LPurvis]

Hi there.

This is a common scenario, where the goal is a single row from many.
In your case you want one row per quote. The "first" item?
Can you describe exactly what that would constitute?

Often you'll hear people wanting to join a parent table to a detail (child) table and return "any" row from the child.
I always feel "if the row choice is random then is there really any purpose to including it?"

Similarly in this case - do you absolutely need the Item fields - to include only one row. The "first" row?
At the very least you'll need some criteria by which to select that which constitutes what the "first" row will be. Given then fields you're returning - it looks like we might just have to go with ItemID (unless there is another in that table which can be used).
Naturally [Quote Num] won't fulfill that role for you - as it is consistent across the whole quote.

George's suggestion (which is aircode remember) is close to what you'd need.
However you can't Join to a corrolated subquery.
You can only refer to such a subquery in Selection or Where clause.
For example

SELECT 
    Q.QuoteDBID, Q.[Quote Num], I.[Items ID], I.Description
FROM 
    Quotes Q
        INNER JOIN 
    Items I
        ON Q.QuoteDBID = I.QuoteDBID
WHERE
    I.[Items ID] In
        (SELECT Top 1 Items.[Items ID] 
         FROM Items 
         WHERE Items.QuoteDBID = I.QuoteDBID 
         ORDER BY Items.[Items ID])
ORDER BY 
    Q.[Quote Num]

Or if updatability isn't an issue then you should get better performance from joining. However this may looks a smidge more convoluted to you.

SELECT 
    Q.QuoteDBID, Q.[Quote Num], I.[Items ID], I.Description
FROM 
    (Quotes Q
        INNER JOIN 
    Items I
        ON Q.QuoteDBID = I.QuoteDBID
    )
        INNER JOIN
    (SELECT Min(I2.[Items ID]) As MinID FROM Items I2 GROUP BY I2.QuoteDBID) X
        ON I.[Items ID] = X.MinID 
ORDER BY 
    Q.[Quote Num]

Again - all aircode. But should be OK. (Test both for responsiveness).

Cheers.

 

[treva26]

Thank you very much for your replies.

Yes I want the Item with the lowest [Items ID]
and the second example works great!

I probably should have mentioned I am using an SQL Server (2005) for the backend and am definitely interested in the fastest way of doing it.

 

[LPurvis]

I was about to say "kudos on the expediency of your testing".
But wow.. that was an hour? Where did it go? :-s
Man, there's not enough time in the day (or even night too!)

Anyway - glad you're sorted.

 

[treva26]

Actually after adding in all the columns I want, I have a problem.

When I put it in a query its fine, but when I:

1. Run it from code with a DoCmd.RunSQL
2. Change the order of the first select statements in the Query SQL view
3. Move it all onto one line in the Query SQL view

It says "Syntax error in FROM clause" !

Can you see a problem with it?

SELECT Quotes.QuoteDBID, Quotes.[Quote Num], Items.[Items ID], Items.Description, Quotes.Prefix, Quotes.[Quote Num], Quotes.Suffix, Quotes.Customer, Quotes.[End User], Quotes.SiteCode, Quotes.[OE Number], Quotes.[Sales Area (Quotes Table)], Quotes.[Quote Arrival Date], Quotes.[Stock Item], Quotes.[Quote Cost Value], Quotes.[Quote Sell Value], Quotes.[Order Cost Value], Quotes.[Order Sell Value], Quotes.[Budget Quote]
FROM (Quotes INNER JOIN Items ON Quotes.QuoteDBID = Items.QuoteDBID) INNER JOIN [SELECT Min(I2.[Items ID]) As MinID FROM Items I2 GROUP BY I2.QuoteDBID]. AS X ON Items.[Items ID] = X.MinID
ORDER BY Quotes.[Quote Num];

 

[LPurvis]

You've stumbled upon my pet peave.
Reformatted (updated syntax) SQL in Access.

SELECT Quotes.QuoteDBID, Quotes.[Quote Num], Items.[Items ID], Items.Description, Quotes.Prefix, Quotes.[Quote Num], Quotes.Suffix, Quotes.Customer, Quotes.[End User], Quotes.SiteCode, Quotes.[OE Number], Quotes.[Sales Area (Quotes Table)], Quotes.[Quote Arrival Date], Quotes.[Stock Item], Quotes.[Quote Cost Value], Quotes.[Quote Sell Value], Quotes.[Order Cost Value], Quotes.[Order Sell Value], Quotes.[Budget Quote]
FROM (Quotes INNER JOIN Items ON Quotes.QuoteDBID = Items.QuoteDBID) INNER JOIN (SELECT Min(I2.[Items ID]) As MinID FROM Items I2 GROUP BY I2.QuoteDBID) AS X ON Items.[Items ID] = X.MinID
ORDER BY Quotes.[Quote Num];

Cheers.