List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:November 19 2005 3:40pm
Subject:Re: BIIIIIIIIIIG query
View as plain text  
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

Thread
BIIIIIIIIIIG querybruno b b magalhães19 Nov
  • Re: BIIIIIIIIIIG queryPeter Brawley19 Nov
  • Re: BIIIIIIIIIIG queryRhino19 Nov
    • Re: BIIIIIIIIIIG querybruno b b magalhães19 Nov
  • Re: BIIIIIIIIIIG queryPaul DuBois19 Nov
  • Re: BIIIIIIIIIIG queryRhino19 Nov
    • Re: BIIIIIIIIIIG querybruno b b magalhães19 Nov
      • Re: BIIIIIIIIIIG query (I GOT IT, well I think so!)bruno b b magalhães19 Nov
    • Re: BIIIIIIIIIIG queryPeter Brawley20 Nov