Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Limiting join-matches to first match

    I have a query between two tables, one is an imported table, and the other a fixed product database.

    With a query I match the imported tables product name with the one in our product database, usually resulting in multiple matches per product, and eventually a mile long match list.

    What I'd like is for the query to rerutn just one match per product, as many of the similar named products in the product database are in fact completely alike, set aise minor differences like table finish, chair cloth color etc. etc.

    Essentially the list simply works to tell the salesperson if the warehouse requested are available in at least one color, finish, model etc. etc. based on the form filled out by the customer. So, I only need that 1 match. Its not very precise, but enough to give the order an initial green light.

    Cheers, Trin
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,138
    Take a look at GROUP BY in SQL
    http://w3schools.com/sql/sql_groupby.asp
    Eg:
    Code:
    people
    firstname lastname age
    george smith 24
    george smith 19
    geoff smith 33
    john smith 85
    
    SELECT firstname, lastname
    FROM people
    GROUP BY firstname, lastname
    
    Results:
    george smith
    geoff smith
    john smith
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2005
    Posts
    183
    I was aware of the Group By clause, but not that it had a First() operator. Brilliant!
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,138
    Quote Originally Posted by Trinsan
    I was aware of the Group By clause, but not that it had a First() operator. Brilliant!
    It does? Fancy posting a link - I want a read!
    George
    Home | Blog

  5. #5
    Join Date
    Oct 2005
    Posts
    183
    Knock yourself out -LINKY. (Aggregate functions)
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,138
    Not supported in 2k - that might be why I've not come across it yet
    Thanks for that - it's going to be a very useful one to add to the arsenal!
    George
    Home | Blog

  7. #7
    Join Date
    Oct 2005
    Posts
    183
    Exactly the same here. I've been used to 97/2000 for a long while. Only last year went 2003, and I've barely started and going 2007 already.

    Then again, I should've known about it, since I do SQL-Server too.
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •