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"
> <brunobbm@stripped>
> To: "MySQL List" <mysql@stripped>
> 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?
>> 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
>
>
