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" <[EMAIL PROTECTED]>
To: "bruno b b magalhães" <[EMAIL PROTECTED]>; "MySQL List"
<mysql@lists.mysql.com>
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?
[EMAIL PROTECTED]
--
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?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
