List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:October 20 2011 2:21pm
Subject:Re: ERROR 1250 (42000): rejected view
View as plain text  
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
Thread
ERROR 1250 (42000): rejected viewhsv20 Oct
  • Re: ERROR 1250 (42000): rejected viewMySQL)20 Oct
    • Re: ERROR 1250 (42000): rejected viewhsv28 Oct