Thanks everybody for the help,
Well, I think I wasn't clear as I'd expected. As Rhino said OUTER and
Peter said INNER (he he he).
As far as I can see, seams Rhino understood it better. But I read
some tutorials and tried some combinations, but none worked as I liked.
For example, I started with only the address part, as phone as emails
are quite similar.
When I use RIGHT OUTTER JOIN only 13 records are displayed, but the
total should be 24. As seams Mysql is excluding records without
addresses matches.
When I use LEFT OUTER JOIN all the 24 records are retrieved BUT all
with the same address, and some NULL columns at the end (for example
contact_id!).
Well, I will try to explain more what I want...
First this system is a huge CRM, and it's built upon modules, for
example contacts.module.php, addresses.module.php, etc.. etc.. etc..
All the functions related to a data is stored in one file, and load
as needed. But in the contacts module, I have a general list of all
contacts in the system. Whe you can search by type, entity, status,
gender, and keyword.
Now the system only searches the contacts table using a FULLTEXT
index. But I would like that this system could search all tables
(addresses, phones and emails), but the problem is that a contact can
or cannot have a related address, phone and email. But there is a
complication. For example an address can be the same for two
contacts, but, for each of these is a commercial address and for the
second it's a billing address... so only the unchangeable dat a is
stored in the address table, and the contact_id, address_id,
address_type_id, etc are stored in a intermediary table which links
those two tables.
So, I still lost, but I am trying to figure it out!
Best regards to you all,
Bruno B B Magalhaes
On Nov 19, 2005, at 2:12 PM, Rhino wrote:
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/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/[EMAIL PROTECTED]
