Inner JOIN

[Azzyh]

Hi i am new to using JOIN, and i think Inner JOIN is the right function that i shall use.

 

So heres what i tried:

$sygo = mysql_query("SELECT member_film.title, member_film.username, member_tutorials.title, member_tutorials.type, member_tutorials.username FROM member_film INNER JOIN member_tutorials ORDER BY dato DESC");

 

Now what i want to do is, to get data's out of those table columns and then show it ordering it all by which dato there's soonest. All the tables do have a column called Dato, and i want it to sort out of that.

 

I think what i did is compleltly wrong, so help me out please.

 

And how do i then echo out the data in a correct way?

[bullbreed]

Hi

 

Here is an INNER JOIN I used yesterday

 

$sql = "SELECT `users`.`name`,`users`.`username`,`users`.`email`,`trainingdata`.`description`,`trainingdata`.`date` FROM `users` INNER JOIN `trainingdata` ORDER BY dato DESC");";

 

My two tables in the statement are 'users' and 'trainingdata' so you seem to have missed out what rows you want to call from your member_tutorials table. Unless you want them all in which case call *

 

[Azzyh]

So let me try:

 

$sql = "SELECT * FROM `member_film` INNER JOIN `member_tutorials` ORDER BY dato DESC");";

 

Is the ORDER by dato correct if you have a column dato in both member_tutorials and member_film?

And what if i want to add a extra table into it, member_tutorials?

- In what way should i echo out the data? Just like normal? Like if there's a column in member_tutorials called title, should i call it this way:

$show = mysql_fetch_array($sql);

echo $show['title'];

[kickstart]

Hi

 

Firstly you want to specify the column(s) to be used to join the tables together with. Ie, the is the common column(s) that you will use to link the rows on. For example I presume you want to join them on the username (but I may be wrong, and you may want to join on other columns as well).

 

Also if a column name is shared between 2 or more tables in your query you need to specify which one you are referring to. Such as Dato in your order by clause.

 

Would give you something like this:-

 

SELECT member_film.title, member_film.username, member_tutorials.title, member_tutorials.type, member_tutorials.username

FROM member_film

INNER JOIN member_tutorials

ON member_film.username =  member_tutorials.username

ORDER BY member_film.dato

 

If you do not specify the column(s) that you are joining the table on then MySQL will return every single possible combination of rows from the 2 tables (ie, if each table had 10 rows you would have 100 rows returned).

 

All the best

 

Keith