In reading this the first time, I came up with the following. I might
be missing something because none of the responses thus far have
mentioned it, but how about:
SELECT ;
ALLCODES.DISPVALUE as POSITION, ;
PERSON.FIRST, ;
PERSON.MIDDLE, ;
PERSON.LAST, ;
PERSON.PHONE ;
FROM PERSON, CONTACTS, ALLCODES ;
WHERE PERSON.PERSID = CONTACTS.PERSID AND ;
CONTACTS.POSCODE = ALLCODES.CODENO AND ;
CONTACTS.CODETYPE = "position"
If the person isn't in the Contacts table then you'll miss those people,
unless you add:
UNION ALL ;
SELECT ;
space(20) as POSITION, ;
PERSON.FIRST, ;
PERSON.MIDDLE, ;
PERSON.LAST, ;
PERSON.PHONE ;
FROM PERSON ;
WHERE PERSON.PERSID NOT IN ;
( SELECT PERSID FROM CONTACTS)
If the CONTACTS.POSID isn't in the ALLCODES table, then you've got a
problem.
> 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).
