I don't have time today to answer your question in detail but the basic
technique you need to use is an _OUTER_ join. Those should be completely
compatible for version 4 and above of MySQL.
A regular join, which is usually called an "inner" or "natural" join,
combines rows of tables where the corresponding keys have equal _NON-NULL_
keys. (A 'null' is a special value that means "unknown" or "not
applicable"). An outer join does the same work as an inner join but also
picks up rows whose keys don't match any of the rows in the other table.
There are three types of outer joins:
- right outer join
- left outer join
- full outer join
[The last time I looked, MySQL didn't directly support the full outer join
but that was a couple of years ago; it may be supported in version 4.1 and
above. Check the manual for yourself to see.]
The "right" and "left" in "right outer join" and "left outer join" refer to
the tables that are on the right and left hand sides of the query. For
example, given:
select name, address, salary
from foo f inner join bar b on f.id = b.idno
"foo" is the left hand table in the join and "bar" is the right hand table
in the join; "foo" appears to the left of "bar" in the FROM clause.
A right outer join does an inner join between the two tables in the join and
then picks up the "orphans" (unmatched rows) from the right hand table.
A left outer join does an inner join between the two tables in the join and
then picks up the orphans from the left hand table.
A full outer join does an inner join between the two tables in the join and
then picks up the orphans from _both_ tables.
I'm sure you can find some tutorials with examples of how to write various
outer joins if you Google on "SQL tutorial". The MySQL manual didn't have
much on joins beyond the statement syntax the last time I looked - many
months ago - but MySQL uses standard SQL so _any_ SQL tutorial should have
some good examples for you, even if it is intended for DB2 or Oracle users.
When you understand the concepts and syntax, you should be able to apply
this information to your specific problem.
Rhino
----- Original Message -----
From: "bruno b b magalhães" <[EMAIL PROTECTED]>
To: "MySQL List" <mysql@lists.mysql.com>
Sent: Saturday, November 19, 2005 9:51 AM
Subject: BIIIIIIIIIIG query
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[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/[EMAIL PROTECTED]
