CODEWITHSUNDEEP
mysqlsqljoininner-joinwhere-clause
Kunal
Kunal

Reputation: 1452

MySQL multiple JOIN clause with multiple WHERE

This is the 3rd edit. Based on all your feedback I was able to generate the following query with multiple search criteria.

Please note that this is an existing system and there budget is an issue so I am trying to do all I can to improve existing queries. The search you see was manually done based on arrays and there was no joins. The same search was taking 2-3 minutes to process whereas thanks to all of you rocking gurus it now takes 7-8 seconds to process :)

SELECT SQL_CALC_FOUND_ROWS fname, lname, desig, company, region, state, country, add_uid, contacts.`id` as id
        FROM contacts
         INNER JOIN contact_to_categories ON contact_to_categories.contactid = contacts.id 
 AND ( 
contact_to_categories.catid = '2'
 ) 

 INNER JOIN contact_professional_details ON contact_professional_details.contact_id = contacts.id
 AND ( 
FIND_IN_SET('1', contact_professional_details.pd_insid)
 OR FIND_IN_SET(' 8', contact_professional_details.pd_insid)
 OR FIND_IN_SET(' 33', contact_professional_details.pd_insid)
 ) 

 AND ( 
FIND_IN_SET('4', contact_professional_details.pd_secid)
 OR FIND_IN_SET('3', contact_professional_details.pd_secid)
 OR FIND_IN_SET('5', contact_professional_details.pd_secid)
 OR FIND_IN_SET('7', contact_professional_details.pd_secid)
 OR FIND_IN_SET('12', contact_professional_details.pd_secid)
 OR FIND_IN_SET('11', contact_professional_details.pd_secid)
 OR FIND_IN_SET('9', contact_professional_details.pd_secid)
 OR FIND_IN_SET('38', contact_professional_details.pd_secid)
 OR FIND_IN_SET('35', contact_professional_details.pd_secid)
 OR FIND_IN_SET('115', contact_professional_details.pd_secid)
 ) 

 INNER JOIN contact_address ON contact_address.contact_id = contacts.id
 AND ( 
contact_address.hmregion IN ('AF', 'EU', 'OC', 'SA')
 OR contact_address.hmcountry IN ('Algeria', 'Angola', 'Benin', 'Comoros', 'Andorra', 'Austria', 'Belarus', 'Belgium', 'American Samoa', 'Australia', 'French Polynesia', 'Guam', 'Kiribati', 'Marshall Islands', 'Colombia', 'Ecuador', 'Falkland Islands', 'Guyana', 'Paraguay', 'Peru', 'Laos', 'Malaysia', 'Myanmar', 'Singapore', 'Vietnam')
 OR contact_address.hmcity = 'singapore'
 ) 

 INNER JOIN contact_offices ON contact_offices.contact_id = contacts.id
 AND ( 
contact_offices.off_region IN ('AF', 'EU', 'OC', 'SA')
 OR contact_offices.off_country IN ('Algeria', 'Angola', 'Benin', 'Comoros', 'Andorra', 'Austria', 'Belarus', 'Belgium', 'American Samoa', 'Australia', 'French Polynesia', 'Guam', 'Kiribati', 'Marshall Islands', 'Colombia', 'Ecuador', 'Falkland Islands', 'Guyana', 'Paraguay', 'Peru', 'Laos', 'Malaysia', 'Myanmar', 'Singapore', 'Vietnam')
 OR contact_offices.off_city = 'singapore'
 ) 


        WHERE 1 AND ( 
FIND_IN_SET('1', contacts.ins_id)
 OR FIND_IN_SET(' 8', contacts.ins_id)
 OR FIND_IN_SET(' 33', contacts.ins_id)
 )

 AND ( 
FIND_IN_SET('4', contacts.sec_id)
 OR FIND_IN_SET('3', contacts.sec_id)
 OR FIND_IN_SET('5', contacts.sec_id)
 OR FIND_IN_SET('7', contacts.sec_id)
 OR FIND_IN_SET('12', contacts.sec_id)
 OR FIND_IN_SET('11', contacts.sec_id)
 OR FIND_IN_SET('9', contacts.sec_id)
 OR FIND_IN_SET('38', contacts.sec_id)
 OR FIND_IN_SET('35', contacts.sec_id)
 OR FIND_IN_SET('115', contacts.sec_id)
 )

 AND ( FIND_IN_SET('Tier 1', `vip_tier`) OR FIND_IN_SET('Tier 3', `vip_tier`) )
 AND ( FIND_IN_SET('Tier A', `vip_coll_tier`) )
 AND ( FIND_IN_SET('Yes', `vip_influencer`) )
 AND ( FIND_IN_SET('Contemporary', `vip_class_art_coll`) OR FIND_IN_SET('Modern', `vip_class_art_coll`) OR FIND_IN_SET('Geographic', `vip_class_art_coll`) )
 AND ( FIND_IN_SET('Sculpture', `vip_med_art_coll`) OR FIND_IN_SET('Photography', `vip_med_art_coll`) OR FIND_IN_SET('Video', `vip_med_art_coll`) OR FIND_IN_SET('Installation', `vip_med_art_coll`) )
 AND ( FIND_IN_SET('Japan', `vip_geo_int`) OR FIND_IN_SET('Korea', `vip_geo_int`) OR FIND_IN_SET('Southeast Asia', `vip_geo_int`) OR FIND_IN_SET('Oceania', `vip_geo_int`) )
 AND ( FIND_IN_SET('HNWI', `vip_seniority`) OR FIND_IN_SET('Top Social Leaders', `vip_seniority`) OR FIND_IN_SET('Other Executives', `vip_seniority`) )
 AND ( `status` = 'a' )

        ORDER BY  fname
                    asc
        LIMIT 0, 50

I know this can be further improved by moving the find in set values to separate tables and creating relational tables between the contacts master table and the values master table. But as I said budget is a big issue for this guys so I guess this is more than efficient for them.

However any further improvement ideas are most welcome.

Upvotes: 1

Views: 514

Answers (4)

Kickstart
Kickstart

Reputation: 21513

Making a major assumption that you are interested in contacts in a specified region OR a specified country OR a specified city, and cleaning your code up a bit:-

SELECT SQL_CALC_FOUND_ROWS fname, lname, desig, company, region, state, country, add_uid, contacts.`id` as id
FROM contacts
INNER JOIN contact_to_categories ON contact_to_categories.contactid = contacts.id AND contact_to_categories.catid = '2'
INNER JOIN contact_professional_details ON contact_professional_details.contact_id = contacts.id
AND (
 FIND_IN_SET('4', contact_professional_details.pd_secid)
 OR FIND_IN_SET('3', contact_professional_details.pd_secid)
 OR FIND_IN_SET('5', contact_professional_details.pd_secid)
 OR FIND_IN_SET('7', contact_professional_details.pd_secid)
 OR FIND_IN_SET('12', contact_professional_details.pd_secid)
 OR FIND_IN_SET('11', contact_professional_details.pd_secid)
 OR FIND_IN_SET('9', contact_professional_details.pd_secid)
 OR FIND_IN_SET('38', contact_professional_details.pd_secid)
 OR FIND_IN_SET('35', contact_professional_details.pd_secid)
 OR FIND_IN_SET('115', contact_professional_details.pd_secid)
)
INNER JOIN contact_address ON contact_address.contact_id = contacts.id 
INNER JOIN contact_offices ON contact_offices.contact_id = contacts.id 
WHERE 1 
AND ((
    contact_address.hmregion IN ('AF', 'EU', 'OC', 'SA')
    OR contact_address.hmcountry IN ('Algeria', 'Angola', 'Benin', 'Comoros', 'Andorra', 'Austria', 'Belarus', 'Belgium', 'American Samoa', 'Australia', 'French Polynesia', 'Guam', 'Kiribati', 'Marshall Islands', 'Colombia', 'Ecuador', 'Falkland Islands', 'Guyana', 'Paraguay', 'Peru', 'Laos', 'Malaysia', 'Myanmar', 'Singapore', 'Vietnam')
    OR contact_address.hmcity='singapore'
)
OR (
    contact_offices.off_region IN ('AF', 'EU', 'OC', 'SA')
    OR contact_offices.off_country IN ('Algeria', 'Angola', 'Benin', 'Comoros', 'Andorra', 'Austria', 'Belarus', 'Belgium', 'American Samoa', 'Australia', 'French Polynesia', 'Guam', 'Kiribati', 'Marshall Islands', 'Colombia', 'Ecuador', 'Falkland Islands', 'Guyana', 'Paraguay', 'Peru', 'Laos', 'Malaysia', 'Myanmar', 'Singapore', 'Vietnam')
    OR contact_offices.off_city='singapore'
)
)
AND (
    FIND_IN_SET('1', contacts.ins_id)
    OR FIND_IN_SET(' 8', contacts.ins_id)
    OR FIND_IN_SET(' 33', contacts.ins_id)
)
AND (
    FIND_IN_SET('4', contacts.sec_id)
    OR FIND_IN_SET('3', contacts.sec_id)
    OR FIND_IN_SET('5', contacts.sec_id)
    OR FIND_IN_SET('7', contacts.sec_id)
    OR FIND_IN_SET('12', contacts.sec_id)
    OR FIND_IN_SET('11', contacts.sec_id)
    OR FIND_IN_SET('9', contacts.sec_id)
    OR FIND_IN_SET('38', contacts.sec_id)
    OR FIND_IN_SET('35', contacts.sec_id)
    OR FIND_IN_SET('115', contacts.sec_id)
)
AND (
    FIND_IN_SET('Tier 1', `vip_tier`) 
    OR FIND_IN_SET('Tier 3', `vip_tier`)
 )
AND (FIND_IN_SET('Tier A', `vip_coll_tier`))
AND (FIND_IN_SET('Yes', `vip_influencer`))
AND (FIND_IN_SET('Contemporary', `vip_class_art_coll`) OR FIND_IN_SET('Modern', `vip_class_art_coll`) OR FIND_IN_SET('Geographic', `vip_class_art_coll`))
AND (FIND_IN_SET('Sculpture', `vip_med_art_coll`) OR FIND_IN_SET('Photography', `vip_med_art_coll`) OR FIND_IN_SET('Video', `vip_med_art_coll`) OR FIND_IN_SET('Installation', `vip_med_art_coll`))
AND (FIND_IN_SET('Japan', `vip_geo_int`) OR FIND_IN_SET('Korea', `vip_geo_int`) OR FIND_IN_SET('Southeast Asia', `vip_geo_int`) OR FIND_IN_SET('Oceania', `vip_geo_int`))
AND (FIND_IN_SET('HNWI', `vip_seniority`) OR FIND_IN_SET('Top Social Leaders', `vip_seniority`) OR FIND_IN_SET('Other Executives', `vip_seniority`))
AND (`status`='a')
ORDER BY  fname asc
LIMIT 0, 50

Note that the use of FIND_IN_SET suggests a poorly normalised database with fields containing comma separated lists of values.

Upvotes: 1

Rahul
Rahul

Reputation: 77866

This is the part which giving error in your query

INNER JOIN contact_professional_details 
ON contact_professional_details.contact_id = contacts.id
 AND (       <-- Here
INNER JOIN contact_to_categories 
ON contact_to_categories.contactid = contacts.id 
 AND (
contact_to_categories.catid = '2'
)

change this to

INNER JOIN contact_professional_details 
ON contact_professional_details.contact_id = contacts.id

INNER JOIN contact_to_categories 
ON contact_to_categories.contactid = contacts.id 
 AND contact_to_categories.catid = '2'

EDIT: Your posted query is total messy, you did join the same table(s) multiple times and did use chained OR condition instead of IN clause. So, below is your modified query.

SELECT SQL_CALC_FOUND_ROWS fname, 
lname, 
desig, 
company, 
region, 
state, 
country, 
add_uid, 
contacts.`id` as id
FROM contacts

INNER JOIN contact_to_categories 
ON contact_to_categories.contactid = contacts.id 
AND contact_to_categories.catid = '2'

INNER JOIN contact_professional_details 
ON contact_professional_details.contact_id = contacts.id
 AND (
 FIND_IN_SET('4', contact_professional_details.pd_secid)
 OR FIND_IN_SET('3', contact_professional_details.pd_secid)
 OR FIND_IN_SET('5', contact_professional_details.pd_secid)
 OR FIND_IN_SET('7', contact_professional_details.pd_secid)
 OR FIND_IN_SET('12', contact_professional_details.pd_secid)
 OR FIND_IN_SET('11', contact_professional_details.pd_secid)
 OR FIND_IN_SET('9', contact_professional_details.pd_secid)
 OR FIND_IN_SET('38', contact_professional_details.pd_secid)
 OR FIND_IN_SET('35', contact_professional_details.pd_secid)
 OR FIND_IN_SET('115', contact_professional_details.pd_secid)
)

 INNER JOIN contact_address ON contact_address.contact_id = contacts.id
 AND 
contact_address.hmregion IN ('AF','EU','OC','SA')
AND 
contact_address.hmcountry IN ('Algeria',
'Angola',
'Benin',
'Comoros',
'Andorra',
'Austria',
'Belarus',
'Belgium',
'American Samoa',
'Australia',
'French Polynesia',
'Guam',
'Kiribati',
'Marshall Islands',
'Colombia',
'Ecuador',
'Falkland Islands',
'Guyana',
'Paraguay',
'Peru',
'Laos',
'Malaysia',
'Myanmar',
'Singapore',
'Vietnam'
)
AND contact_address.hmcity='singapore'

 INNER JOIN contact_offices ON contact_offices.contact_id = contacts.id
 AND 
contact_offices.off_region IN ('AF','EU','OC','SA')
AND
contact_offices.off_country IN ('Algeria',
 'Angola',
 'Benin',
 'Comoros',
 'Andorra',
 'Austria',
 'Belarus',
 'Belgium',
 'American Samoa',
 'Australia',
 'French Polynesia',
 'Guam',
 'Kiribati',
 'Marshall Islands',
 'Colombia',
 'Ecuador',
 'Falkland Islands',
 'Guyana',
 'Paraguay',
 'Peru',
 'Laos',
 'Malaysia',
 'Myanmar',
 'Singapore',
 'Vietnam'
)
AND contact_offices.off_city='singapore'



WHERE 1 AND (
FIND_IN_SET('1', contacts.ins_id)
 OR FIND_IN_SET(' 8', contacts.ins_id)
 OR FIND_IN_SET(' 33', contacts.ins_id)
 )

 AND (
FIND_IN_SET('4', contacts.sec_id)
 OR FIND_IN_SET('3', contacts.sec_id)
 OR FIND_IN_SET('5', contacts.sec_id)
 OR FIND_IN_SET('7', contacts.sec_id)
 OR FIND_IN_SET('12', contacts.sec_id)
 OR FIND_IN_SET('11', contacts.sec_id)
 OR FIND_IN_SET('9', contacts.sec_id)
 OR FIND_IN_SET('38', contacts.sec_id)
 OR FIND_IN_SET('35', contacts.sec_id)
 OR FIND_IN_SET('115', contacts.sec_id)
 )

 AND (FIND_IN_SET('Tier 1', `vip_tier`) OR FIND_IN_SET('Tier 3', `vip_tier`))
 AND (FIND_IN_SET('Tier A', `vip_coll_tier`))
 AND (FIND_IN_SET('Yes', `vip_influencer`))
 AND (FIND_IN_SET('Contemporary', `vip_class_art_coll`) OR FIND_IN_SET('Modern', `vip_class_art_coll`) OR FIND_IN_SET('Geographic', `vip_class_art_coll`))
 AND (FIND_IN_SET('Sculpture', `vip_med_art_coll`) OR FIND_IN_SET('Photography', `vip_med_art_coll`) OR FIND_IN_SET('Video', `vip_med_art_coll`) OR FIND_IN_SET('Installation', `vip_med_art_coll`))
 AND (FIND_IN_SET('Japan', `vip_geo_int`) OR FIND_IN_SET('Korea', `vip_geo_int`) OR FIND_IN_SET('Southeast Asia', `vip_geo_int`) OR FIND_IN_SET('Oceania', `vip_geo_int`))
 AND (FIND_IN_SET('HNWI', `vip_seniority`) OR FIND_IN_SET('Top Social Leaders', `vip_seniority`) OR FIND_IN_SET('Other Executives', `vip_seniority`))
 AND (`status`='a')

ORDER BY  fname asc
LIMIT 0,50

Upvotes: 4

geoand
geoand

Reputation: 63991

Your inner join conditions are not correct.

For example 

INNER JOIN contact_to_categories ON contact_to_categories.contactid = contacts.id
AND
(
    contact_to_categories.catid = '2'
)

needs to be

INNER JOIN contact_to_categories ON (
        contact_to_categories.contactid = contacts.id
    AND contact_to_categories.catid = '2'
)

You will need to fix all the inner joins you have that follow the aforementioned pattern

Upvotes: 2

Thanos Markou
Thanos Markou

Reputation: 2623

When you have multiple joins, its better to use alias for your tables instead of the whole name. Makes reading and understanding much easier. Anyway try this near line 9 where you get the error. 

  INNER JOIN contact_professional_details
    ON contact_professional_details.contact_id = contacts.id
    INNER JOIN contact_to_categories ON contact_to_categories.contactid = contacts.id 
    AND contact_to_categories.catid = '2'

Upvotes: 3

Related Questions