SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Level 8 Chinese guy Archbob's Avatar
    Join Date
    Sep 2001
    Location
    Somewhere in this vast universe
    Posts
    3,741
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting from 3 tables at once

    Code:
    $getarticles="SELECT * from b_articles as a, b_users as b,b_posts as c where c.articleidentifier=a.ID and b.userID=a.authorid";
    Ok basically I want the select to select all articles from b_articles and display the article(which is in the articles table, the author(in users table), and display the thread relating to the article(in posts table)).

    If there are no threads, then give option to start a thread.

    However, this query is not working, its turning up blank. It was working fine until I add the third table into the query. Can anyone tell me how to do this correctly?

  2. #2
    SitePoint Evangelist Daijoubu's Avatar
    Join Date
    Oct 2002
    Location
    Canada QC
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    http://dev.mysql.com/doc/mysql/en/pr...ith-alias.html
    No aliasing in WHERE clause?

    And btw, what error does it report? Use mysql_error() on it
    Speed & scalability in mind...
    If you find my reply helpful, fell free to give me a point

  3. #3
    Level 8 Chinese guy Archbob's Avatar
    Join Date
    Sep 2001
    Location
    Somewhere in this vast universe
    Posts
    3,741
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There's no error in it, it just comes up blank because c.articleidentifier=a.ID and b.userID=a.authorid specifies both those conditions have to be met whereas I want it to select if only the second one is met. I need it to pull information from all three tables though.

    However, if I take out the first, then it selects the same entry like a billion times. Not sure what to do.

  4. #4
    SitePoint Evangelist Daijoubu's Avatar
    Join Date
    Oct 2002
    Location
    Canada QC
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How about
    PHP Code:
    SELECT FROM b_articles AS ab_posts AS c
     LEFT JOIN b_users b ON 
    (b.userID=a.authorid)
    WHERE c.articleidentifier=a.ID 
    Speed & scalability in mind...
    If you find my reply helpful, fell free to give me a point

  5. #5
    Level 8 Chinese guy Archbob's Avatar
    Join Date
    Sep 2001
    Location
    Somewhere in this vast universe
    Posts
    3,741
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks.

    I'm not used to Left Joins.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •