Try:
SELECT FF.name AS thename,
MAX(FF.label) AS thelabel,
F.name AS fieldsname
FROM regformfields as FF
INNER JOIN regfields as F
ON (FF.name = F.Name)
WHERE FF.label != ''
GROUP BY FF.name, F.name
I don't think you can include the ORDER BY F.saveorder (another column) in
this case, unless you include it (F.saveorder) in the SELECT and GROUP BY
list.
HTH,
Tore.
----- Original Message -----
From: "Scott Brown" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, February 22, 2003 1:57 PM
Subject: Query syntax help?
> OK, I am having a bit of trouble designing a MySQL query that returns what
> I want. Here is the query as I have it thus far:
>
> SELECT DISTINCT regformfields.name AS thename,
> regformfields.label AS thelabel,
> regfields.name AS fieldsname
> FROM regformfields
> INNER JOIN regfields ON (regformfields.name = regfields.Name) WHERE
> regformfields.label != ''
> ORDER BY regfields.saveorder;
>
> In this particular query, there can be multiple occurrences of thename(can
> be filtered by DISTINCT), therefore multiple occurrences of thelabel
(which
> can't be filtered by DISTINCT, as it is always different for the same
> thename), but fieldsname is always unique.
>
> I don't care which thename or which thelabel is returned, but I only want
> one (these two tables, together with some others, construct a schema for
> yet others...), i.e thename = 'email' may be returned twice in this result
> set, but I only want it to appear once. DISTINCT, as it is used here, does
> not return what I want, as thelabel will rarely, if ever, be distinct.
>
> The ideal query would force the DISTINCT to be related ONLY to thename,
and
> return whatever thelabel it happens to grab, based on however it is
> indexing, which would be the first saveorder it stumbles upon.
>
> Any help would be appreciated!
>
> TIA,
> --Scott Brown
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
