Jump to content


Photo

Joining Tables


  • Please log in to reply
4 replies to this topic

#1 Gaia

Gaia
  • Members
  • PipPipPip
  • Advanced Member
  • 95 posts
  • LocationCanada

Posted 29 October 2005 - 03:11 AM

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 :)

#2 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 29 October 2005 - 01:08 PM

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
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 Gaia

Gaia
  • Members
  • PipPipPip
  • Advanced Member
  • 95 posts
  • LocationCanada

Posted 29 October 2005 - 01:20 PM

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 ^_^

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 29 October 2005 - 05:33 PM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 Gaia

Gaia
  • Members
  • PipPipPip
  • Advanced Member
  • 95 posts
  • LocationCanada

Posted 30 October 2005 - 08:59 PM

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 ^_^




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users