From: Peter Brawley Date: November 19 2005 3:40pm Subject: Re: BIIIIIIIIIIG query List-Archive: http://lists.mysql.com/mysql/191889 Message-Id: <437F477E.9040409@earthlink.net> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit 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) > 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