Bruno,
>when the contact has an address, phone and email it works just fine, but
>I want it to display those how doesnt also! Could someone with JOINs
>experience help me?
Your FROM t1,t2,... WHERE condition1, condition2... joins are INNER, so
they exclude contacts without addresses, phone numbers &c.
It's much easier--for understanding, designing and debugging queries--to
write explicit joins, for example
...FROM flx_contacts AS contacts
INNER JOIN flx_contacts_addresses AS addresses,
ON contacts.contact_id = addresses.contact_id ...
will return only the contacts which have matching rows in addresses, but
... FROM flx_contacts AS contacts
LEFT JOIN flx_contacts_addresses AS addresses,
ON contacts.contact_id = addresses.contact_id ...
will return contacts with and without addresses, with NULLs in the
address columns for those contacts without addresses.
So I suggest that you rewrite your query using this syntax, specifying
INNER or LEFT as needed.
PB
-----
bruno b b magalhães wrote:
Hi guys,
I need some help with an query that I know I must use joins, but I
can't figure out how to.
The problem is, when the contact has an address, phone and email it
works just fine, but I want it to display those how doesnt also!
Could someone with JOINs experience help me?
And one more question, how compatible are joins? I mean, the
environment I am developing in is MySQL 4.1, but some of my clients
are using 4.0.25 and others are using 5.0.
The query is this:
========================================================================
================
SELECT SQL_CACHE
/* CONTACTS FIELDS */
contacts.contact_id AS id,
contacts.contact_code AS code,
contacts.contact_name AS name,
contacts.contact_tax_id AS tax_id,
contacts.contact_birth AS birth,
(YEAR(CURDATE())-YEAR(contacts.contact_birth))-(RIGHT(CURDATE(),5)
<RIGHT(contacts.contact_birth,5)) AS age,
contacts.contact_timezone AS timezone,
/* CONTACTS RELATED FIELDS */
contact_entities.contact_entity_name AS entity_name,
contact_genders.contact_gender_name AS gender_name,
contact_types.contact_type_name AS type_name,
contact_types.contact_type_level AS type_level,
contact_statuses.contact_status_name AS status_name
FROM
/* CONTACTS TABLES */
flx_contacts AS contacts,
flx_contacts_to_contacts AS contacts_to_contacts,
/* CONTACTS RELATED TABLES */
flx_contact_entities AS contact_entities,
flx_contact_genders AS contact_genders,
flx_contact_types AS contact_types,
flx_contact_statuses AS contact_statuses,
/* ADDRESSES TABLES */
flx_contacts_addresses AS addresses,
flx_'contacts_address_types AS address_types,
flx_contacts_to_addresses AS contacts_to_addresses,
/* PHONES TABLES */
flx_contacts_phones AS phones,
flx_contacts_phone_types AS phone_types,
'flx_contacts_to_phones AS contacts_to_phones,
/* EMAILS TABLES */
flx_contacts_emails AS emails,
flx_contacts_email_types AS email_types,
flx_'contacts_to_emails AS contacts_to_emails
/* JOINING CONTACTS RELATED TABLES */
WHERE
contacts.contact_id =
contacts_to_contacts.contact_child_id
AND
contacts.contact_entity_id =
contact_entities.contact_entity_id
AND
contacts.contact_gender_id = contact_genders.contact_gender_id
AND
contacts.contact_type_id = contact_types.contact_type_id
AND
contacts.contact_status_id =
contact_statuses.contact_status_id
AND
contacts_to_contacts.contact_parent_id = 0
/* JOINING ADDRESSES TABLES */
AND
addresses.address_id =
contacts_to_addresses.address_id
AND
contacts_to_addresses.contact_id = contacts.contact_id
AND
contacts_to_addresses.address_type_id =
address_types.address_type_id
/* JOINING PHONES TABLES */
AND
phones.phone_id = contacts_to_phones.phone_id
AND
contacts_to_phones.contact_id = contacts.contact_id
AND
contacts_to_phones.phone_type_id = phone_types.phone_type_id
/* JOINING EMAILS TABLES */
AND
emails.email_id =
contacts_to_emails.email_id
AND
contacts_to_emails.contact_id = contacts.contact_id
AND
contacts_to_emails.email_type_id =
email_types.email_type_id
/* SEARCHING */
AND
MATCH (contacts.contact_code, contacts.contact_name,
contacts.contact_tax_id) AGAINST ('KEYWORD' IN BOOLEAN MODE)
OR
MATCH (addresses.address_line_one, addresses.address_line_two,
addresses.address_line_three, addresses.address_zipcode,
addresses.address_neighborhood, addresses.address_city,
addresses.address_state, addresses.address_country) AGAINST
('KEYWORD' IN BOOLEAN MODE)
OR
MATCH (phones.phone_countrycode, phones.phone_areacode,
phones.phone_number) AGAINST ('KEYWORD' IN BOOLEAN MODE)
OR
MATCH (emails.email_address) AGAINST ('KEYWORD' IN BOOLEAN MODE)
GROUP BY
contacts.contact_id
ORDER BY
contacts.contact_name
ASC
========================================================================
================
Many thanks in advance!
Best Regards,
Bruno B B Magalhaes
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.4/175 - Release Date: 11/18/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
