>I am trying to select data using ODBC to reach an Access Database and was wondering if someone could give me the valid ODBC SQL syntax for the following:
>
>Tables:
>
>Parent
>Child
>StatusLookup
>PriorityLookup
>
>Parent is linked to child by ItemKey
>Parent has a link to valid status in StatusLookup via a StatusKey
>Child has a link to valid Priority in PriorityLookup via PriorityKey
>
>User will filter on Parent.UserName
>
>So if I wanted to do this straight I would do
>
>SELECT Parent.Username, Parent.Phone, Child.KidFirstName, Child.KidLastName,
>StatusLookup.Status, PriorityLookup.Priority
>FROM Parent, Child, StatusLookup, PriorityLookup
>WHERE Parent.ItemKey = Child.ItemKey AND
>Parent.StatusKey = StatusLookup.StatusKey AND
>Child.PriorityKey = PriorityLookup.PriorityKey AND
>Parent.Username = "SMITHBOB"
>
>So now the ODBC problem. I need the above to be translated to an ODBC recognized statement that will give me all Parent records even when the parent has no children. So I need to do one of those great Joins, the problem is the ODBC syntax I have tried does not work. Can someone help here? Remember I still need the child and the Priority also linked through the child. TIA

Brett,
Would you try this :

SELECT P.Username, P.Phone, C.KidFirstName, C.KidLastName,
S.Status, Pr.Priority
FROM force Parent P ;
left join StatusLookup S on P.StatusKey = S.StatusKey ;
left join Child C on P.ItemKey = C.ItemKey ;
left join PriorityLookup Pr on C.PriorityKey = Pr.PriorityKey ;
WHERE P.Username = "SMITHBOB"

Cetin