>>>>CREATE SQL VIEW CALLBACK AS ;
>>>>SELECT ALL Servcalls.*, Customers.company;
>>>> FROM appdata!servcalls LEFT JOIN appdata!customers ;
>>>> ON Servcalls.prim_key = Customers.prim_key ;
>>>> GROUP BY Servcalls.prim_key;
>>>> ORDER BY Servcalls.callbacktime
>>>
>>Thanks Larry,
>>
>>The returned records where 2 different servcalls each matching a different customer. The problem was in the group by condition which blocked additional records for the same two customers being returned.
>>
>Hi Curtis,
>Are you saying that there are many records in the Servcalls table with the same value in Prim_Key? If so, then Prim_Key is not a primary key as I thought from the name. It is the foreign key of Customers. I didn't worry about the GROUP BY clause because I saw what I thought to be an error in the JOIN condition.
>
>Did you by chance use some third-party Entity Relation Diagraming tool to design your database? One of the *features" of these tools is to create a field with the same name in the child table when you define a foreign key relationship. If your field names are generic (e.g. Prim_Key), this could lead to misunderstandings with others that do not know your system as well as you do.
>
>Anyway, I'm glad you got it working.
Hi Larry,
>>Are you saying that there are many records in the Servcalls table with the same value in Prim_Key? If so, then Prim_Key is not a primary key as I thought from the name. It is the foreign key of Customers. <<
Help me out here, I used the name prim_key because the key is a unique one to the customer generated by a 3rd party framework for the parent and child. However, I had the choice of name and I always thought the parent and child fields where suppose to have the same name when they where to be used to set a one to many relation.
Should the field names be different?
-Curtis
- jim durkin
"The universe is a big place, perhaps the biggest." Kurt Vonnegut