List:General Discussion« Previous MessageNext Message »
From:bruno b b magalhães Date:November 19 2005 6:56pm
Subject:Re: BIIIIIIIIIIG query
View as plain text  
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
>
>

Thread
BIIIIIIIIIIG querybruno b b magalhães19 Nov
  • Re: BIIIIIIIIIIG queryPeter Brawley19 Nov
  • Re: BIIIIIIIIIIG queryRhino19 Nov
    • Re: BIIIIIIIIIIG querybruno b b magalhães19 Nov
  • Re: BIIIIIIIIIIG queryPaul DuBois19 Nov
  • Re: BIIIIIIIIIIG queryRhino19 Nov
    • Re: BIIIIIIIIIIG querybruno b b magalhães19 Nov
      • Re: BIIIIIIIIIIG query (I GOT IT, well I think so!)bruno b b magalhães19 Nov
    • Re: BIIIIIIIIIIG queryPeter Brawley20 Nov