It looks like your ABS SQL implementation does not handle aliases. You'll have
to play with it to make the sub-query work with the main query.
You are probably close with your reformulated query - I see you are using a
state number instead of my 'CA' and 'FL' strings.
An explanation of my example SQL line:
(select state from junk j1 where j2.name=j1.name order by state limit 1) as
state
This sub-query re-searches the name/state table, looking for the current
person-name. It orders by the state name and only outputs one result- the first
one it finds in alphabetical order. Since you are mostly interested in CA, then
that one should come first.
Since you are using a numerical state ID, simply insure that CA's id is lower
than FL's id. It probably already is, if you entered the states in alphabetical
order.
Back to the query...
If CA is in the list, then that is the state that appears in the result. If FL
is in the list and no CA, then FL appears in the list.
All other states are ignored, but that is done in the
"where state in ('CA', 'FL')"
step.
My advise is to use either a command line database query utility designed for
your database or a GUI one that allows you to enter SQL queries.
Play with different queries until your result sets look like you want.
Once you get your query working, then you can implement it in Delphi.
HTH!
Kevin G. McCoy
--- In [email protected], "Perry Kappetein" <pkappet...@...> wrote:
>
> I am actually using ABS database
>
> and handles SQL statements
>
> I have this now
>
> select romname,(SELECT location from roms j1 where j2.romname=j1.romname
> order by location limit 1) as location
>
> FROM roms j2 where location in('1','7') group by romnumber
>
>
>
> but getting the error
>
>
>
> [6/29/2009 5:45:48 PM] Executing Query:
>
> select romname,(SELECT location from roms j1 where j2.romname=j1.romname
> order by location limit 1) as location
>
> FROM roms j2 where location in('1','7') group by romnumber
>
> [6/29/2009 5:45:48 PM] Token 'FROM' expected, but '1' found at line 1,
> column 98 - Native error: 30184
>
>
>
> Roms is my table name and Romname is a String and Location is a string
>
>
>
> _____
>
> From: [email protected] [mailto:[email protected]] On Behalf
> Of Kevin McCoy
> Sent: Monday, June 29, 2009 5:27 PM
> To: [email protected]
> Subject: [delphi-en] Re: Is this possible? if so how
>
>
>
>
>
> This probably isn't optimal, but here is a SQL query that works in MySQL and
> does what you want.
>
> ------------------------
> SELECT name,
> (select state from junk j1 where j2.name=j1.name order by state limit 1) as
> state
> FROM junk j2
> where state in ('CA', 'FL')
> group by name
> ------------------------
>
> Junk is a 3 column table:
> ID : integer - unique id
> Name:string
> state:string
>
> I added the ID column just to make it easy to edit for me.
>
> I am not an Access programmer, so I have no idea if you can do such a query
> in that DB.
>
> --- In delphi...@yahoogrou <mailto:delphi-en%40yahoogroups.com> ps.com,
> "Perry Kappetein" <pkappetein@> wrote:
> >
> > yes, your right.
> > messed up my example
> >
> > it's in a access table, that gets loaded into VirtualTree (Soft-gems.net)
> >
> > I was trying to do it within virtualTree, but it's getting me nowhere.
> >
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
