Joining Tables

[Gaia]

hey,

 

I know there are ways to join information in 2 or more tables by using LEFT/RIGHT JOIN MySQL commands.

 

I've read the mysql.com documentation on it and it still confuses me. I don't understand why there are single letters placed before the table name ( m.foo, s.boo ) etc.

 

Does anyone have a tutorial or site that explains LEFT/RIGHT JOIN alittle better?

 

Thanks

[obsidian]

not really a tutorial, but i think i might be able to help you understand a bit better... whenever you declare a table name in a SQL statement, you can name the table whatever you want. usually people use a single letter to help shorten their code. when you are referencing two tables that have the same field name, you must declare a table name before that field name since it would be a duplicate reference otherwise. for instance:

 

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * FROM table1, table2 WHERE table1.id = '$id' AND table2.user = table1.id;

[!--sql2--][/div][!--sql3--]

 

this can also be written like this (notice the letters and references are different:

 

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * FROM table1 a, table2 b WHERE a.id = '$id' AND b.user = a.id;

[!--sql2--][/div][!--sql3--]

 

hope this helps

[Gaia]

Thanks for the explination. I finally understand it!

 

Just on more question. I noticed you didn't use a LEFT/RIGHT JOIN in there. What specifically would those be used for and would you mind creating a query with on of them in it and exlaining it to me like you did in your last post?

 

Thanks, i really appreciate it

[Barand]

In this query

 

SELECT * FROM table1 a, table2 b WHERE a.id = '$id' AND b.user = a.id;

 

the WHERE clause is specifying the join condition (b.user = a.id)

 

This is an alternative syntax and is equivalent to

 

SELECT * FROM table1 a INNER JOIN table2 b 
ON b.user = a.id
WHERE a.id = '$id' 

 

I always recommend the latter

1 ) it produces faster queries

2 ) it separates the relationship info from the search criteria and IMO shows more clearly what the query is doing.

[Gaia]

hmm ok i think i'm starting to understand this alittle better. I will mark this solved until i come across any more questions while trying it out