How to do more than one table join in MySQL

This post has moved here.

This entry was posted on Friday, November 17th, 2006 at 8:49 am and is filed under PHP / MySQL, mysql, php, programming, web development. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

11 Responses to “How to do more than one table join in MySQL”

  1. ian douglas Says:
    November 17, 2006 at 11:55 pm

    And if you want to select everything from one table, plus other fields, you could do something like:

    SELECT a.*,c.owner,d.priceOfOwner FROM animal a, farmers c, …

    (note that the ‘as’ keyword is optional)

    Just need to be careful that using the wildcard like that doesn’t result in overlapping columns. For example, if the ‘animal’ table and ‘farmers’ table don’t each have an ‘owner’ field, otherwise you’d have to do something like:

    SELECT a.*, c.owner as c_owner,d.priceOfOwner …

  2. Andres - author of this article Says:
    December 6, 2006 at 8:12 pm

    I notice a lot of people landing on this page. Unfortunately, wordpress does not give me stats on how long you stay… So, question to people if they made it this far down the page, has this post been useful to you and how can I make it better?

  3. Peter Says:
    January 27, 2007 at 6:18 pm

    I stayed for hours ;-)

    Thanks – from the first 2 pages of Google search on “mysql table joins basic” (without quotes), this one was by far the most helpful for a newbie!

  4. James Says:
    March 22, 2007 at 12:51 pm

    The article is great, but actually I have a problem which I can’t find the answer to anywhere.

    I have an SQL join, testing it in the phpAdmin console, it returns the result I want.

    The trouble is extracting the data in PHP. The result joins 2 tables, ‘article’ and ‘articleText’, which both have a column called ‘id’. So when I try to reference id (via row{‘id’}), it returns id from the articleText table, when I actually want id from the article table.

    How do I specify which id I want? I tried row{‘article.id’} but this just returns nothing (just blank space).

  5. Andres Says:
    December 18, 2007 at 12:16 am

    Sorry, missed the previous comment.

    The problem is most likely the {} braces. row['id'] will probably work. Just in case, your query should look like this:

    $sql = SELECT a.id
    FROM article AS a, articleText AS b
    WHERE a.id = “3” AND a.id= b.id

    That should only store the id you want (z.id) in your php request variable. Here’s what the rest of your php could look like:

    $res = mysql_query($sql) or die (‘could not retrieve data’) – Error: ‘.mysql_error()); //NOTE: remove everything within die() if making your website public!

    $row = mysql_fetch_array($res, MYSQLASSOC);

  6. Sharpstyx Says:
    December 19, 2007 at 2:41 pm

    This helped me so much i have been going in circles for days trying to figure this out

  7. Andres Says:
    December 19, 2007 at 7:33 pm

    Glad to help. =)

  8. yomi Says:
    February 25, 2008 at 9:21 am

    I have a project i’m working and trying to retreive information from 3tables. they are
    Balance(AccountNumber,AccountName,AccountBalance),Deposits(AccountNumber,DateDeposited,AmountDeposited),Withdrawals(AccountNumber,Datwwithdrawn,AmountWithdrawn).

    this is the query i wrote:
    select Balance.AccountNumber,AccountName,AccountBalance,DateDeposited,AmountDeposited,DateWithdrawn,AmountWithdrawn from balance inner join Deposits on Balance.AccountNumber = Deposits.AccountNumber inner join Withdrawals on Deposits.AccountNumber = Withdrawals.AccountNumber;

    I have made many modifications to this query only to get error mesages.

    I’ll appreciate your help in finding the error and posting your suggested solution to me.

  9. Andres Says:
    February 29, 2008 at 11:02 pm

    Hi Yomi,

    At first glance, looks like you need to capitalize balance after FROM. You might have to add BalanceDot to every column name specific to that table, not sure.

    If that doesn’t work, it might be easier to rewrite your query to follow the above tutorial because it seems you’ll achieve the same with or without inner join (see the conclusion of this thread http://forums.mysql.com/read.php?24,196098,196098#msg-196098). Also, a little easier to read and debug. My PHP/MySQL guru friend Ian Douglas from IanDouglas.com has this to say:

    Essentially, the inner joins are the same as writing the query like this:
    select Balance.AccountNumber, AccountName, AccountBalance, DateDeposited, AmountDeposited, DateWithdrawn, AmountWithdrawn
    FROM Balance, Deposits, Withdrawls
    WHERE
    Balance.AccountNumber = Deposits.AccountNumber
    AND Deposits.AccountNumber = Withdrawals.AccountNumber;

    Good luck.
    -A

  10. umesh sharma Says:
    May 23, 2008 at 7:44 pm

    i am very thanks full because i learn many things about queryes

  11. ламинат Says:
    August 24, 2008 at 4:19 pm

    0xI’ll thingk about it.0s I compleatly agree with last post. xxg
    ламинат купить 3c

Leave a Reply