Sql Join Query Issue

Question

I've a mysql table called tutor_signup_form is look like this

id    feerange    gender    name
1     20          male      alex      
2     10          female    Robin
3     15          male      Babu

Now I've a html search form where few drop down selected fields and many Chckbox fields are exist. I want to get the search result from multiple table using sql INNER JOIN. My INNER JOIN query is look like this

// search query
$query =  mysql_query("SELECT tutor_signup_form . *, tutor_signup_edu_psle . *,
tutor_signup_edu_olevel . *, tutor_signup_edu_alevel . *, tutor_signup_edu_diploma . *,
tutor_signup_edu_degree . *, tutor_signup_edu_masters . *, tutor_signup_edu_psd . *,
tutor_signup_level_primary . *, tutor_signup_level_olevel . *, tutor_signup_level_alevel . *,
tutor_signup_level_int . *, tutor_signup_level_uni . *, tutor_signup_level_music . *,
tutor_signup_level_lang . *, tutor_signup_level_com . *, tutor_signup_pre_central . *,
tutor_signup_pre_east . *, tutor_signup_pre_west . *, tutor_signup_pre_south . *,
tutor_signup_pre_north . *, tutor_signup_pre_ne . *, tutor_signup_pre_nw . *  FROM tutor_signup_form

INNER JOIN tutor_signup_edu_psle ON tutor_signup_form.tutor_id = tutor_signup_form.tutor_id     
INNER JOIN tutor_signup_edu_olevel ON tutor_signup_form.tutor_id = tutor_signup_edu_olevel.tutor_id
INNER JOIN tutor_signup_edu_alevel ON tutor_signup_form.tutor_id = tutor_signup_edu_alevel.tutor_id 
INNER JOIN tutor_signup_edu_diploma ON tutor_signup_form.tutor_id = tutor_signup_edu_diploma.tutor_id
INNER JOIN tutor_signup_edu_degree ON tutor_signup_form.tutor_id = tutor_signup_edu_degree.tutor_id
INNER JOIN tutor_signup_edu_masters ON tutor_signup_form.tutor_id = tutor_signup_edu_masters.tutor_id
INNER JOIN tutor_signup_edu_psd ON tutor_signup_form.tutor_id = tutor_signup_edu_psd.tutor_id
INNER JOIN tutor_signup_level_primary ON tutor_signup_form.tutor_id = tutor_signup_level_primary.tutor_id
INNER JOIN tutor_signup_level_olevel ON tutor_signup_form.tutor_id = tutor_signup_level_olevel.tutor_id
INNER JOIN tutor_signup_level_alevel ON tutor_signup_form.tutor_id = tutor_signup_level_alevel.tutor_id
INNER JOIN tutor_signup_level_int ON tutor_signup_form.tutor_id = tutor_signup_level_int.tutor_id
INNER JOIN tutor_signup_level_uni ON tutor_signup_form.tutor_id =  tutor_signup_level_uni.tutor_id
INNER JOIN tutor_signup_level_music ON tutor_signup_form.tutor_id = tutor_signup_level_music.tutor_id
INNER JOIN tutor_signup_level_lang ON tutor_signup_form.tutor_id = tutor_signup_level_lang.tutor_id
INNER JOIN tutor_signup_level_com ON tutor_signup_form.tutor_id =  tutor_signup_level_com.tutor_id
INNER JOIN tutor_signup_pre_central ON tutor_signup_form.tutor_id = tutor_signup_pre_central.tutor_id
INNER JOIN tutor_signup_pre_east ON tutor_signup_form.tutor_id = tutor_signup_pre_east.tutor_id
INNER JOIN tutor_signup_pre_west ON tutor_signup_form.tutor_id = tutor_signup_pre_west.tutor_id
INNER JOIN tutor_signup_pre_south ON tutor_signup_form.tutor_id = tutor_signup_pre_south.tutor_id
INNER JOIN tutor_signup_pre_north ON tutor_signup_form.tutor_id =  tutor_signup_pre_north.tutor_id
INNER JOIN tutor_signup_pre_ne ON tutor_signup_form.tutor_id = tutor_signup_pre_ne.tutor_id
INNER JOIN tutor_signup_pre_nw ON tutor_signup_form.tutor_id = tutor_signup_pre_nw.tutor_id 

WHERE tutor_signup_form.feerange <= '$budget'");

For example I selected budget field = 20. So my query should be product only 3 results from tutor_signup_form but it's produce 24 results. I don't understand why it's produce 24 result. Can you fix it ?

Update:

enter image description here

On a (very important) aside; you really should read up on database normalization ... — Sherlock
– Sherlock, Commented Aug 25, 2014 at 12:02
@Sherlock in this case I think it's the main matter not 'aside' :) — Zentoaku
– Zentoaku, Commented Aug 25, 2014 at 12:06
The biggest INNER JOIN I've even seen. You should offer a bounty for this ;) — hex494D49
– hex494D49, Commented Aug 25, 2014 at 12:14
Well, I'll try to help you... first of all, delete your second and third join since they are duplicated - you're using them twice and check the results. — hex494D49
– hex494D49, Commented Aug 25, 2014 at 12:33

DRapp · Accepted Answer · 2014-08-25 12:53:08Z

1

As Deadman mentioned, Cartesian...

Your first inner join was a copy/paste of table names

INNER JOIN tutor_signup_edu_psle 
   ON tutor_signup_form.tutor_id = tutor_signup_form.tutor_id

should have been

INNER JOIN tutor_signup_edu_psle 
   ON tutor_signup_form.tutor_id = tutor_signup_edu_psle.tutor_id

I have not read past that, but will in the mean-time

In addition, you have all your joins as INNER JOINs. If someone does not have a record in one of the tables (such as Music), but does tutoring in Computers, and someone searches for Computers, they will not be returned. I suggest changing to LEFT JOIN for all of them, so if the record exists in the tutoring subsidiary level, you are good and won't loose it.

answered Aug 25, 2014 at 12:53

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Shibbir Over a year ago

Oh! Awesome. I was blind!. Thanks a lot!

DRapp Over a year ago

@Shibbir, see revised answer regarding left vs inner join.

Shibbir Over a year ago

Another great suggestion. You are Rock. I need to learn php and mysql in depth.

DRapp Over a year ago

@Shibbir, it's not just PHP, but querying in general. The syntax between engines may be slightly different, but the principles are the same across them all, especially getting into inner, left, right, outer joins. Also, a suggestion, use aliases on table names to keep your queries shorter and more readable...

munsifali · Accepted Answer · 2014-08-25 12:14:07Z

0

If I've got it right you need to check your where clause.i think Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table

answered Aug 25, 2014 at 12:14

munsifali

1,7432 gold badges24 silver badges44 bronze badges

Collectives™ on Stack Overflow

Sql Join Query Issue

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related