SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    May 2002
    Location
    Louisville, KY
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to query several tables in one string?

    Hey all,

    Forgive my newbieness... I just discovered these forums and they ROCK!

    How do I query several tables but get one piece of info? I'm so confused... I use phpmyadmin to practice but I always get like 4 or 5 rows back of details. Am I not being specific enough in the WHERE clause?

    example: DB=test and Tables=tbl1, tbl2, tbl3
    (all of which have the same types of colums)
    PHP Code:
    $slq "SELECT * FROM tdb1, tdb2, tdb3 WHERE username = $uid AND article = $artID"
    Does this make sense? I'm new to joins and trying to understand them. Perhaps someone can offer advise.

    THANKS!!
    JP

  2. #2
    SitePoint Guru DeNasio's Avatar
    Join Date
    May 2001
    Posts
    830
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    After the WHERE clause you should specify what the tables have in common.

    Example:

    Let's say you have the following tables:

    table "a":
    +----+------+
    | a1 | a2 |
    +----+------+
    | 10 | u |
    | 20 | v |
    | 30 | w |
    | 40 | x |
    | 50 | y |
    | 60 | z |
    +----+------+


    table "b":
    +----+------+
    | b1 | b2 |
    +----+------+
    | 10 | p |
    | 20 | q |
    +----+------+

    Code:
    SELECT * FROM a,b;


    The code above will give you the following results:

    +----+------+----+------+
    | a1 | a2 | b1 | b2 |
    +----+------+----+------+
    | 10 | u | 10 | p |
    | 20 | v | 10 | p |
    | 30 | w | 10 | p |
    | 40 | x | 10 | p |
    | 50 | y | 10 | p |
    | 60 | z | 10 | p |
    | 10 | u | 20 | q |
    | 20 | v | 20 | q |
    | 30 | w | 20 | q |
    | 40 | x | 20 | q |
    | 50 | y | 20 | q |
    | 60 | z | 20 | q |
    +----+------+----+------+


    Code:
    SELECT * FROM a,b WHERE a.a1 = 20;


    The code above will give you the following results:

    +----+------+----+------+
    | a1 | a2 | b1 | b2 |
    +----+------+----+------+
    | 20 | v | 10 | p |
    | 20 | v | 20 | q |
    +----+------+----+------+

    Code:
    SELECT * FROM a,b WHERE a.a1 = 20 AND a.a1=b.b1;


    The code above will give you the following results:

    +----+------+----+------+
    | a1 | a2 | b1 | b2 |
    +----+------+----+------+
    | 20 | v | 20 | q |
    +----+------+----+------+

    Hope this helps.

    Ballot-Box.net - free polls for webmasters
    FormLog.com - free form processor

  3. #3
    SitePoint Addict
    Join Date
    May 2002
    Location
    Louisville, KY
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It does a little. I'm not sure why all the extra data is coming up... like in SELECT * from a,b .... why does it double the data in each column? Maybe I just need to read more about SQL.

  4. #4
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Head over to mysql.com and look at the SELECT query syntax. You'll learn about using DISTINCT and LIMIT.
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  5. #5
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jpfour23
    It does a little. I'm not sure why all the extra data is coming up... like in SELECT * from a,b .... why does it double the data in each column? Maybe I just need to read more about SQL.
    What you're doing is called a cross-join, that gies you all combinations of fields. You need to do an equi-join, as DeNasio siad.

    Here are some tutorials from devshed you may find useful:

    Understanding SQL Joins

    MySQL Table Joins

    Matt.


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
  •