Limiting join-matches to first match

Trinsan · #1 (**permalink**) 06-11-07, 05:19

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

gvee · #2 (**permalink**) 06-11-07, 05:58

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

Trinsan · #3 (**permalink**) 06-11-07, 08:31

I was aware of the Group By clause, but not that it had a First() operator. Brilliant!

gvee · #4 (**permalink**) 06-11-07, 08:33

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!

Trinsan · #5 (**permalink**) 06-11-07, 09:17

Knock yourself out -LINKY. (Aggregate functions)

gvee · #6 (**permalink**) 06-11-07, 09:34

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!

Trinsan · #7 (**permalink**) 06-12-07, 04:44

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech