View

[WWinter]

>I am having problems getting the desired results out of a view.
>
>I am using a contact table that contains, employee, vendors, and customers.
>
>and a message table that contains messages from all contact types.
>
>
>I am trying to return all the messages for a contact that is also an employee
>
>What I mean is if message is put into the message table from a contact to an employee and that employee returns a message to contact my view should have two records in it for the contact that is an emlpoyee but I am not getting this, it looks like i am only getting on side of the results no mater what I do.
>
>In the contact table I have contact_id and employee_id and in message table I also have contact_id and employee_id I have both tables in the view and have tried differant conections between how should i do this if it can be done.

 

[ramani]

HI Billwinter, if you have taken SQL code of your view and posted it, we could do something to identify the problem.
Now the above question is just confusing without proper information. All the reason may be why no one answered.

Let me try and do something now..

Contacts table.. has ContactId, EmployeeId.. etc.

Messages Table.. has ContactId, EmployeeId, messageText

SO if you want to extract all messages between the employeeId and a contactId..

SELECT * FROM messagesTable where EmployeeId = myCHoice
OR
SELECT * FROM messagesTable where ContactId = myCHoice



ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[WWinter]

Hi ramani here is the SQL that is being generated in the view designer.

SELECT Contact.pkey_contact, Message.*, Contact.employee_pkey_id;
FROM data1!contact LEFT OUTER JOIN data1!message ;
ON Contact.pkey_contact = Message.pkey_contact;
WHERE Contact.pkey_contact = ?vp_Contact.pkey_contact;
ORDER BY Message.date

with this i am getting the contact side but not the messages for the employee id for the contact.

Thanks Bill

 

[ramani]

Hi

I find your code should work.. and dont have an answer immediate.

A quick try..

SELECT Contact.pkey_contact, Message.*, Contact.employee_pkey_id;
FROM data1!contact LEFT OUTER JOIN data1!message ;
ON Contact.employee_pkey_id = Message.employee_pkey_id;
WHERE Contact.pkey_contact = ?vp_Contact.pkey_contact;
ORDER BY Message.date

Are you getting a problem of seeing only Employee records ?

ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[WWinter]

Thanks for the reply, I am only seeing the message that the contact sent, not message the message sent to an employee.