Hi, Mandy. SQL is like bike riding - you have to fall off a lot, and then you find you're cruising along without thinking about it.

Your first SELECT is fine, as you said. Lets try to get a position name into your contacts.

SELECT contacts.persid, allcodes.dispvalue from contacts, allcodes;
WHERE contacts.poscode = allcodes.codeno

This will give you your persid field with the position name. Now lets combine this with the PERSON file to get a name & position

SELECT Person.first, Person.middlei, Person.last, Person.phone, ;
Person.email, allcodes.dispvalue FROM Person, contacts, allcodes;
WHERE person.persid = contacts.persid;
AND contacts.poscode = allcodes.codeno;
INTO cursor MyCursor order by 3

This links the person into contacts (by the 2 persid fields) and from there into Allcodes by the poscode & codeno fields. IF you have a contacts record for every person.persid AND an allcodes record for every contacts.poscode, you'll get all your data. If not, you need to do a join. I've ordered it by last name (field 3), just for fun.

This SQL can be done in the view designer. Set up your 3 tables and link them, then just pull in the fields you want.

HTH
Barbara


>I'm losing it here. I've been reading about SQL SELECT for the past 24 hours but can't get the syntax right for this one view I'm trying to create. Here's what I'm trying to do...
>
>The final view columns should be:
>Position/Title.......First.........Middle........Last......Phone
>
>First, Middle, Last & Phone come from my Person.dbf
>
>Position/Title comes from the following:
>
>Person.persid related to Contacts.persid which also contains Contacts.poscode which then relates to Allcodes.codeno for Allcodes.codetype="Position". Here are partial table strucs to better illustrate.
>
>Person.dbf -|-----< Contacts.dbf >-----|- Allcodes.dbf
> persid acode* codetype
> first persid codeno
> middle poscode dispvalue
> etc.
>
>(*acode [agency code] relates to an agency table to indicate which agency a person belongs to ... another thing/filter to deal with later)
>
>I think I need to write a SQL Select statement with a Union Clause to create the view I need. So far I created this for the Person info which works:
>
> SELECT Person.first,Person.middlei,Person.last,Person.phone, ;
> Person.email FROM Person
>
>and I tried this to get the person's position without any success (I think you call it a Cartesian Join ;^) ...):
>
> SELECT Contacts.persid,Contacts.poscode,Allcodes.dispval ;
> FROM Contacts,Allcodes ;
> WHERE Allcodes.codetype='position' ;
> AND Contacts.poscode=Allcodes.codeno
>
>I was trying to get two SELECTs which would work individually then combine them with a UNION Clause but haven't got a clue as to how to do this especially how I tell VFP (v3.0 btw) to only relate the Allcodes.codeno's that are a particular codetype ("position" in this case).
>
>Any help (very detailed code if possible ;^)!) would be incredibly appreciated. Once I get this, I think I'll be on a roll for awhile with my forms.
>
>MTIA!
>Mandy