Help With Mysql Query Join Tables

[mamer 0]

So often I need to query several table that are linked through some key.I never understood the difference between different types of JOIN although I used it in one of my application, but it just worked without knowing how.The main problem with using the wrong JOIN is that you get lots of duplicated records no matter you try to filter the results using WHEREMy question is general and not specific to a certain case. I only need to understand the difference between the different types of Join.Can any expert help me?Thanks

[k_nitin_r 8]

Mamer,Typically, when you perform a normal join between two or more tables, you should get the values you need. The only case in which you would have duplications is when the join condition is improper or if the tables were not normalized. With the ANSI style joins, you do not have to use the WHERE clause of the SELECT statement unless you want to filter the results further.

[rpgsearcherz 5]

So often I need to query several table that are linked through some key.
I never understood the difference between different types of JOIN although I used it in one of my application, but it just worked without knowing how.

The main problem with using the wrong JOIN is that you get lots of duplicated records no matter you try to filter the results using WHERE

My question is general and not specific to a certain case. I only need to understand the difference between the different types of Join.

Can any expert help me?

Thanks

I'm still a bit new to this but I'll take a try at this.

There are a few different types of join:

LEFT JOIN -> always shows the left column *even if there is no matching right column*
RIGHT JOIN -> always shows the right column *even if there is no matching left column*
FULL JOIN -> only shows when both columns are hit
JOIN -> shows regardless, as long as one is hit

[mamer 0]

Thank you both for the explanation. I tried this and it worked: $sql = "SELECT a.*, s.*, u.* "; $sql .= "FROM tbl_assignment a "; $sql .= " RIGHT JOIN tbl_assignment_submissions s ON a.id = s.assignment "; $sql .= " RIGHT JOIN tbl_user u ON s.userid = u.id "; $sql .= " RIGHT JOIN tbl_course_display cd ON s.userid = cd.userid "; $sql .= "WHERE ( s.data2 != '' "; $sql .= "OR s.numfiles != '0') "; $sql .= "AND a.name like '%Assignment%' "; $sql .= "ORDER BY firstname, name ASC";But when I use FULL JOIN it gives sql syntax error.Any reason for this?