List:General Discussion« Previous MessageNext Message »
From:bruno b b magalhães Date:November 19 2005 2:51pm
Subject:BIIIIIIIIIIG query
View as plain text  
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
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