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