This is what I originally tried to do. I belive when you write a query like this however, that the alias (outer_key here) is not defined inside the scope of the subquery. My second try was the Outer/Inner join. This will return too many rows. The group by was initially appealing too, until I realized that none of the aggregate functions would return the correct record. I think the way this needs to be done, is very similar to this query. The change that you would need to make would be to change the subquery to a function call that returns the correct id. This way you can pass in outer_key as a parameter to the subquery. Unfortunately the server I am developing on is mySQL 4.x, and does not support stored procedures or functions. Maybe there is another way, I'm still brain storming. Ben Liyanage ben at smartankgroup.com 410.336.2464 -----Original Message----- From: Bill Carr [mailto:bill at bottlenose-wine.com] Sent: Wednesday, December 14, 2005 10:50 AM To: Ben Liyanage Subject: Re: [Hidden-tech] Mind Puzzle For All You SQL Gurus On Dec 13, 2005, at 6:50 PM, Ben Liyanage wrote: > ** Be a Good Dobee and help the group, you must be counted to post > . > ** Fill out the survey/skills inventory in the member's area. > I have a sql challenge for those of you who wish to accept it. > > I am trying to write a query on two tables, with a many to one > relationship. > Let us call the primary table A, and the foreign key table B. For > each record in table A, I would like 1 (and only 1) corisponding > record from table B. The 1 record should be the result of the top > record from a sort on table B. SELECT A.key AS outer_key, A.col1, (SELECT B.col2 FROM B WHERE outer_key=B.key ORDER BY some_col LIMIT 1) FROM A Bill Carr Bottlenose - Wine & Spirits eBusiness Specialists (877) 857-6700 http://www.bottlenose-wine.com Download vCard