Here is my query:
(SELECT DISTINCT contacts.id, contacts.photo, Title, FirstName,
LastName FROM Contacts
JOIN Contacts_To_Households ON Contacts.id =
Contacts_To_Households.ContactId
JOIN Households ON Contacts_To_Households.HouseholdId = Households.Id
JOIN Customers ON Households.customerId = Customers.Id
WHERE Customers.id = '<customer.id>')
UNION
(SELECT DISTINCT contacts.id, contacts.photo, Title, FirstName,
LastName FROM Contacts
JOIN Contacts_To_Departments ON Contacts.id =
Contacts_To_Departments.ContactId
JOIN Departments ON Contacts_To_Departments.DepartmentId =
Departments.id
JOIN Branches ON Departments.BranchID = Branches.id
JOIN Companies ON Branches.CompanyID = Companies.id
JOIN Customers ON Companies.customerId = Customers.Id
WHERE Customers.id = '<customer.id>')
This query behaves beautifully, but for various reasons I need to
know which table combinations a row is compiled from.
I'd like to add a 'Virtual Field' to the union, 'Kind', which would
have the value "Household" if from the first set and "Company" from
the second set.
Any other ways to differentiate the rows would also be welcome.
And before you ask, it is important that all of these values be
returned in a single query....
Any ideas??
- Tom
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>
