>I'm using VFP 6.0
>
>I have a household table that has a state_id field and a city_id that are related into the states table, and city table respectively.
>
>When i create a local view by using the view designer, it works great, but when i try to save it, I get a message "SQL: column city_id is not found", I can save this view, but cannot there after edit it. If I take off one or the other related tables (either city or states table) , all's ok. This is the SQL that it generates:
>
>SELECT DISTINCT *;
> FROM gems!household INNER JOIN gems!states;
> INNER JOIN gems!city ;
> ON Household.city_id = City.city_id ;
> ON Household.state_id = States.state_id;
> ORDER BY Household.household_id
>
>So I tried taking off the joins and creating filters, in which case it generates "where" clauses and alls ok and i can save this.... but this not does not let me take advantage of the diff joins, if i needed to.
>
>SELECT DISTINCT *;
> FROM gems!household, gems!city, gems!states;
> WHERE Household.state_id = States.state_id;
> AND Household.city_id = City.city_id;
> ORDER BY Household.household_id
>
>Now, I can write these SQLs and save them, but why can't I does it not work via the view designer ???
>
>can anyone help ?????
>

Because the view designer is buggy with multiple joins. You can do the basic design in View Designer, view the SQL and do a copy-paste of the code. Open up a PRG file and paste the code. Next step is the un-nest the joins and make them hierarchical:

Create SQL View View_Name AS ;
SELECT DISTINCT * ;
 FROM  gems!household ;
  INNER JOIN gems!states ;
   ON  Household.state_id = States.state_id ;
  INNER JOIN gems!city ;
   ON  Household.city_id = City.city_id ;
 ORDER BY Household.household_id