[Hidden-tech] Mind Puzzle For All You SQL Gurus

Ben Liyanage ben at smartankgroup.com
Wed Dec 14 11:27:32 EST 2005


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




Google

More information about the Hidden-discuss mailing list