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:
FROM Address
LEFT JOIN Nam
ON ...
LEFT JOIN Paid
ON ...
Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
