List:General Discussion« Previous MessageNext Message »
From:bruno b b magalhães Date:November 19 2005 8:53pm
Subject:Re: BIIIIIIIIIIG query (I GOT IT, well I think so!)
View as plain text  
Hy everybody,

I think I solved the problem... Well it's working on my computer... :)
Take I look... If you have any remarks, PLEASE don't hesitate!

======================================================================== 
======
SELECT SQL_CACHE
					contacts.contact_id										AS id,
					contacts.contact_code									AS code,
					contacts.contact_name									AS name,
					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_statuses.contact_status_name					AS status_name
				FROM
					flx_contacts				AS contacts
				
				LEFT OUTER JOIN
					flx_contacts_to_contacts	AS contacts_to_contacts
				ON
					contacts.contact_id = contacts_to_contacts.contact_child_id
					
				LEFT OUTER JOIN
					flx_contact_entities		AS contact_entities
				ON
					contacts.contact_entity_id = contact_entities.contact_entity_id
					
				LEFT OUTER JOIN
					flx_contact_genders			AS contact_genders
				ON
					contacts.contact_gender_id = contact_genders.contact_gender_id
					
				LEFT OUTER JOIN
					flx_contact_types			AS contact_types
				ON
					contacts.contact_type_id = contact_types.contact_type_id
					
				LEFT OUTER JOIN
					flx_contact_statuses		AS contact_statuses
				ON
					contacts.contact_status_id = contact_statuses.contact_status_id

				LEFT OUTER JOIN
					flx_contacts_to_addresses	AS contacts_to_addresses
				ON
					contacts.contact_id = contacts_to_addresses.contact_id
				
				LEFT OUTER JOIN
					flx_contacts_addresses		AS contacts_addresses
				ON
					contacts_to_addresses.address_id = contacts_addresses.address_id
				
				LEFT OUTER JOIN
					flx_contacts_to_phones	AS contacts_to_phones
				ON
					contacts.contact_id = contacts_to_phones.contact_id
				
				LEFT OUTER JOIN
					flx_contacts_phones		AS contacts_phones
				ON
					contacts_to_phones.phone_id = contacts_phones.phone_id
				
				LEFT OUTER JOIN
					flx_contacts_to_emails	AS contacts_to_emails
				ON
					contacts.contact_id = contacts_to_emails.contact_id
				
				LEFT OUTER JOIN
					flx_contacts_emails		AS contacts_emails
				ON
					contacts_to_emails.email_id = contacts_emails.email_id
				
				WHERE
					contacts.contact_id IS NOT NULL
				AND MATCH (contacts.contact_code, contacts.contact_name,  
contacts.contact_tax_id) AGAINST ('+KEYWORD' IN BOOLEAN MODE)
				OR MATCH (contacts_addresses.address_line_one,  
contacts_addresses.address_line_two,  
contacts_addresses.address_line_three,  
contacts_addresses.address_zipcode,  
contacts_addresses.address_neighborhood,  
contacts_addresses.address_city, contacts_addresses.address_state,  
contacts_addresses.address_country) AGAINST ('+KEYWORD' IN BOOLEAN MODE)
				OR MATCH (contacts_phones.phone_countrycode,  
contacts_phones.phone_areacode, contacts_phones.phone_number) AGAINST  
('+KEYWORD' IN BOOLEAN MODE)
				OR MATCH (contacts_emails.email_address) AGAINST ('+KEYWORD' IN  
BOOLEAN MODE)  GROUP BY contacts.contact_id  ORDER BY  
contacts.contact_name  ASC

==========================================================

Course this is the final version outputted from PHP module before  
it's send to the database.

Best Regards,
Bruno B B Magalhaes

On Nov 19, 2005, at 6:17 PM, bruno b b magalhães wrote:

> Another test.
>
> I tried just to join the contacts table with the  
> contacts_to_addresses table, well worked just perfect. But I am not  
> able to figure it out how to join the addresses table on same join!
>
> Regards,
> Bruno B B Magalhaes
>
> On Nov 19, 2005, at 5:30 PM, Rhino wrote:
>
>> Paul,
>>
>> Do you have any idea if MySQL plans to support full outer joins at  
>> some point in the future? I realize you can probably fake them  
>> without having the syntax available but it would be nice to be  
>> able to get them directly....
>>
>> Rhino
>>
>> ----- Original Message ----- From: "Paul DuBois" <paul@stripped>
>> To: "bruno b b magalhães" <brunobbm@stripped>; "MySQL List"  
>> <mysql@stripped>
>> Sent: Saturday, November 19, 2005 12:05 PM
>> Subject: Re: BIIIIIIIIIIG query
>>
>>
>> At 12:51 -0200 11/19/05, 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.
>>
>> Re: compatibility, it would be a good idea to read this section of  
>> the
>> Reference Manual with regard to the changes made to join  
>> processing in
>> MySQL 5.0.12 for compliance with standard SQL:
>>
>> http://dev.mysql.com/doc/refman/5.0/en/join.html
>>
>> -- 
>> Paul DuBois, MySQL Documentation Team
>> Madison, Wisconsin, USA
>> MySQL AB, www.mysql.com
>>
>> -- 
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql? 
>> unsub=rhino1@stripped
>>
>>
>> -- 
>> No virus found in this incoming message.
>> Checked by AVG Free Edition.
>> Version: 7.1.362 / Virus Database: 267.13.4/175 - Release Date:  
>> 18/11/2005
>>
>>
>>
>>
>> -- 
>> No virus found in this outgoing message.
>> Checked by AVG Free Edition.
>> Version: 7.1.362 / Virus Database: 267.13.4/175 - Release Date:  
>> 18/11/2005
>>
>>
>> -- 
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql? 
>> unsub=brunobbm@stripped
>>
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql? 
> unsub=brunobbm@stripped
>
>

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