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
