Some help in INNER JOIN COMMAND

[genzedu777]

Hi all,

 

I have some questions regarding INNER JOIN.

 

Currently I have 4 tables...

1) tutor_preferred_district (tutor_preferred_district.jpg)

2) tutor_preferred_dlocation  (tutor_preferred_dlocation.jpg) (Which has tutor_id in it)

 

3) tutor_preferred_zone (tutor_preferred_zone.jpg)

3) tutor_preferred_zone (tutor_preferred_zlocation.jpg) (Which has tutor_id in it)

 

I thought of querying a $query, which consists of all these 4 tables tied to a common ID called 'tutor_id'. How do I achieve it? Below are the codes, I know the codes are wrong, I should have used INNER JOIN, but apparently I do not know how to join it, hence I have used twice of 'FROM' command, hopefully you can understand what I would like to achieve

 

<?php
$query = "SELECT tpd.district_id, tpd.district_name, tpz.zone_id, tpz.zone_name	  
                 FROM tutor_preferred_dlocation AS tpdl 
                 FROM tutor_preferred_zlocation AS tpzl 
                 WHERE tpdl.tutor_id = '{$tutor_id}';"
?>

 

Thank you so much

 

[attachment deleted by admin]

[Jessica]

You don't have any joins in that query. You have two "froms".

 

Instead of the second "from" you need a "inner join" and after than an "on".

[genzedu777]

Hi jesirose,

 

Appreciate your reply. I understand how INNER JOIN works, but currently I am trying to join the 4 tables together into ONE query.

 

How do I achieve it? Below are my codes, do you think it's possible in the first place?

 

<?php
$query = "SELECT tpd.district_id, tpd.district_name, tpz.zone_id, tpz.zone_name
       (1)
	 FROM tutor_preferred_district AS tpd
	 INNER JOIN tutor_preferred_dlocation AS tpdl USING (district_id)
	 //How to join (1) & (2) together?
	 (2)
	 INNER JOIN tutor_preferred_zlocation AS tpzl USING (???)
	 INNER JOIN tutor_preferred_zone AS tpz USING (zone_id)

                 WHERE tpdl.tutor_id = '{$tutor_id}';"
?>

[Jessica]

You got the first part, now you need "ON"

[genzedu777]

Hi jesirose,

 

Really appreciate if you can be more specific? What do you mean by

You got the first part, now you need "ON"

 

If it's possible, could you elaborate more in the codes? Thanks

 

$query = "SELECT tpd.district_id, tpd.district_name, tpz.zone_id, tpz.zone_name
       (1)
       FROM tutor_preferred_district AS tpd
       INNER JOIN tutor_preferred_dlocation AS tpdl USING (district_id)
       //How to join (1) & (2) together?
       (2)
       INNER JOIN tutor_preferred_zlocation AS tpzl USING (???)
       INNER JOIN tutor_preferred_zone AS tpz USING (zone_id)

                 WHERE tpdl.tutor_id = '{$tutor_id}';"

 

 

[fenway]

Why not the same field as for dlocation?

[Jessica]

I could be more specific, but if you had looked up the documentation for a join you would see what ON is.

IMO http://www.gotapi.com/ is a great source for docs. Add the MySQL version and look up JOIN. Read it and look for the word ON. then read the examples. Then try it and post your new code. If your new code doesn't work, we can go from there.

[genzedu777]

Hi jesirose,

 

I understand where you are coming from, if you look carefully at the 4 pictures or tables...

 

//The primary key for tutor_preferred_dlocation.sql is district_id, and that is the reason why I can link tutor_preferred_dlocation & tutor_preferred_district together, because they shared the same common key

1) tutor_preferred_dlocation  (tutor_preferred_dlocation.jpg)

2) tutor_preferred_district (tutor_preferred_district.jpg)

 

//The primary key for tutor_preferred_zlocation.sql is zone_id, and that is the reason why I can link tutor_preferred_zlocation & tutor_preferred_zone  together, because they shared the same common key

3) tutor_preferred_zlocation (tutor_preferred_zlocation.jpg)

4) tutor_preferred_zone (tutor_preferred_zone.jpg)

 

Now, my first question

1) Can I still link (1)(2) with (3)(4), is it possible?

2) If it's possible, then how? I have read through LEFT JOIN & RIGHT JOIN, even ON. I don't seem to find an answer, the common denominator is still...you need a common primary key to link the tables together.

 

Please correct me if I am wrong as I am still new in php and sql, would appreciate any advice from you. Thanks

 

[Jessica]

The key doesn't have to have the same name, if that's what you're asking.

 

 

If there is NO way for you to tell what a tutor's preferred X is by any keys then you need to redesign your db.