Hello Hal�sz S�ndor,
On 10/19/2011 17:50, wrote:
> I made this query a view, called "MEMBERP", no problem:
> SELECT MemberID, ereStart, DateModified, MembershipExpires, MemberSince, Category,
> Boardster, GROUP_CONCAT(DISTINCT Salutation ORDER BY Rank) AS Salutation,
> GROUP_CONCAT(DISTINCT GivenName ORDER BY Rank) AS GivenName, GROUP_CONCAT(DISTINCT Surname
> ORDER BY Rank) AS Surname, Street, City, State, Zip, HomePhone, Comments,
> GROUP_CONCAT(DISTINCT WorkPhone ORDER BY Rank) AS WorkPhone, GROUP_CONCAT(DISTINCT
> CellPhone ORDER BY Rank) AS CellPhone, GROUP_CONCAT(DISTINCT Email ORDER BY Rank) AS
> eMail, MAX(Volunteer) AS Volunteer, MAX(ReceivesFlyer) AS ReceivesFlyer, Houmuch, Wherat
> FROM Nam RIGHT JOIN Address USING (MemberID) LEFT JOIN Paid USING (MemberID)
> GROUP BY MemberID
> ORDER BY Surname, GivenName
> There are tables "Nam" and "Address", and "Paid" is a view.
> But when I try to use it for a table, there is trouble:
> mysql> select * from memberp;
> ERROR 1250 (42000): Table 'nam' from one of the SELECTs cannot be used in field list
> If I leave this, "GROUP_CONCAT(DISTINCT GivenName ORDER BY Rank) AS", out, there is
> no trouble. If I leave out any of the other like phrases, there is yet trouble. If I leave
> the "ORDER BY ..." out, there is no trouble.
> Only "GivenName" is derived from "GROUP_CONCAT" and is also a lesser field for
> ordering by. Why is that a problem?
Perhaps it is the USING clause that is messing you up. The USING()
clause needs to pick one source for MemberID and you appear to have two.
Try converting to a more explicit ON clause instead.
Also you are mixing LEFT and RIGHT joins in the same query. While
technically not wrong, it's also not 'good form'. I suggest you alter
the sequence of your table definitions to use either all LEFT or all
RIGHT like this:
LEFT JOIN Nam
LEFT JOIN Paid
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN