Hello everybody,
I have a next problem to get a query working.
I have some tables. A User has Phone numbers and Calls associated with
each phone.
# Example: All the calls of the user number 1
SELECT
Call.*
FROM
User, Phone, Call
WHERE
Phone.User=1 AND
Call.Phone=Phone.PhoneNumber
A User has also an agenda with ContactGroups an Contacts, where the
contact has also a phone number.
#Al the contacts for the user 1
SELECT
Contact.*
FROM
User, ContactGroup, Contact
WHERE
ContactGroup.User=1 AND
Contact.ContactGroup=ContactGroup.ContactGroupId
What I want to retrieve is a list with all the calls, but with the
contact associated to the called number (if that contact exists) or a
text showing that the call is no assigned to a contact.
I have tried something like this:
SELECT
IFNULL(Contact.Name, 'Not Assigned'), Call.*
FROM
User, Phone, Call
LEFT JOIN
ContactGroup
ON
ContactGroup.User=1
LEFT JOIN
Contact
ON
Contact.ContactGroup=ContactGroup.ContactGroupId
WHERE
(Contact.PhoneNumber=Call.CalledNumber OR Contact.PhoneNumber IS
NULL) AND
Phone.User=1 AND
Call.Phone=Phone.PhoneNumber
It works with calls that have the called number in the user contact
list, but the query repeat the calls for each group that don't have
received calls (the effect of the "OR Contact.PhoneNumber IS NULL").
I don't know if I'm missing something or this cannot be done with MySql.
I think that this could be done with a Temp table, but I need to do it
in only a query.
Any idea about how to do it?
Regards
Javier
*Note: The tables are simplified because they were named in Spanish and
there were more tables used to filter the calls, but they are not
related with my problem
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
