Re: BIIIIIIIIIIG query
Bruno,
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?
Your FROM t1,t2,... WHERE condition1, condition2... joins are INNER, so
they exclude contacts without addresses, phone numbers c.
It's much easier--for understanding, designing and debugging queries--to
write explicit joins, for example
...FROM flx_contacts AS contacts
INNER JOIN flx_contacts_addresses AS addresses,
ON contacts.contact_id = addresses.contact_id ...
will return only the contacts which have matching rows in addresses, but
... FROM flx_contacts AS contacts
LEFT JOIN flx_contacts_addresses AS addresses,
ON contacts.contact_id = addresses.contact_id ...
will return contacts with and without addresses, with NULLs in the
address columns for those contacts without addresses.
So I suggest that you rewrite your query using this syntax, specifying
INNER or LEFT as needed.
PB
-
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.
The query is this:
SELECT SQL_CACHE
/* CONTACTS FIELDS */
contacts.contact_idAS id,
contacts.contact_codeAS code,
contacts.contact_nameAS name,
contacts.contact_tax_idAS tax_id,
contacts.contact_birthAS birth,
(YEAR(CURDATE())-YEAR(contacts.contact_birth))-(RIGHT(CURDATE(),5)
RIGHT(contacts.contact_birth,5))AS age,
contacts.contact_timezoneAS timezone,
/* CONTACTS RELATED FIELDS */
contact_entities.contact_entity_nameAS entity_name,
contact_genders.contact_gender_nameAS gender_name,
contact_types.contact_type_nameAS type_name,
contact_types.contact_type_levelAS type_level,
contact_statuses.contact_status_nameAS status_name
FROM
/* CONTACTS TABLES */
flx_contactsAS contacts,
flx_contacts_to_contactsAS contacts_to_contacts,
/* CONTACTS RELATED TABLES */
flx_contact_entitiesAS contact_entities,
flx_contact_gendersAS contact_genders,
flx_contact_typesAS contact_types,
flx_contact_statusesAS contact_statuses,
/* ADDRESSES TABLES */
flx_contacts_addressesAS addresses,
flx_'contacts_address_typesAS address_types,
flx_contacts_to_addressesAS contacts_to_addresses,
/* PHONES TABLES */
flx_contacts_phonesAS phones,
flx_contacts_phone_typesAS phone_types,
'flx_contacts_to_phonesAS contacts_to_phones,
/* EMAILS TABLES */
flx_contacts_emailsAS emails,
flx_contacts_email_typesAS email_types,
flx_'contacts_to_emailsAS 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,
Re: BIIIIIIIIIIG query
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: BIIG 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
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/[EMAIL PROTECTED]
Re: BIIIIIIIIIIG query
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: BIIG 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,
Re: BIIIIIIIIIIG query
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: BIIG 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/[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]
Re: BIIIIIIIIIIG query
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: BIIG 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/[EMAIL PROTECTED]
Re: BIIIIIIIIIIG query (I GOT IT, well I think so!)
)
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: BIIG 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]
Re: BIIIIIIIIIIG query
Hi Rhino, I believe I have seen full outer join on a 5.1 to-do list, but at the moment I can't recall where. PB - 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: BIIG 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 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.4/175 - Release Date: 11/18/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
