SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Hybrid View

  1. #1
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECTing from multiple tables

    I have two tables that are related through a primary key.

    The tables are:

    catagories
    catId (PK)
    catName

    details
    catId
    make
    model
    etc...

    The "catId" field is the primary key for the "catagories" table, and there is no primary key for the "details" table.

    This is the first time I have tried to select information from multiple tables at once.

    What I need to know, is if it is possible (and I believe it is) to select the "catName" from the "catagories" table and all the fields from the "details" table in one query. If so, how would you write the query?

    And how would you extract the information from the result set?

    Thanks!
    John

  2. #2
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try:

    Code:
    SELECT c.*, d.* FROM categories c, details d
    WHERE c.catID = d.catID;

  3. #3
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll give that a try, but what do "c" and "d" stand for?
    John

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2002
    Location
    Illinois
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is how I would go about doing it, it might not be right but it should work with how your database is setup.

    PHP Code:

    $sql 
    mysql_query("SELECT *, catName FROM catagories, details");

      while (
    $row mysql_fetch_array($sql)) {
          
    $catName $row["catName"];
          
    $make $row["make"];
          
    $model $row["model"];
          echo(
    "$catName , $make$model"):
         } 
    but some questions, why not have the database tables setup like this:


    catagories
    Id (PK)(AUTO)
    catName
    detailsID

    details
    Id (PK)(AUTO)
    make
    model
    etc...

    OR

    catagories
    Id (PK)(AUTO)
    catName

    details
    Id (PK)(AUTO)
    catId
    make
    model
    etc...

    I think that would help when trying to pick one particalar row out of the table with a SELECT JOIN query, but hey I haven't been doing this that long myself so I could be way off.

    Hope this Helps
    CC
    -- Hit any key to continue. "Where's the any key?" --

  5. #5
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something like:

    Code:
    SELECT catName, make, model
    FROM catagories as c, details as d
    WHERE c.catId = d.catId AND c.catId = 1
    c and d are aliases to save you typing catagories (spelt wrong ) and details

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  6. #6
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "categories" Thanks. Haste in setup...

    You said:
    PHP Code:
    SELECT catNamemakemodel
    FROM catagories 
    as cdetails as d
    WHERE c
    .catId d.catId AND c.catId 
    Now, I'm going to walk through this one step at a time to see if I understand it.

    SELECT catName, make, model (Does the order of the selected fields make a difference based on the order of the tables in the FROM clause? And if I want to select JUST the catName from the "categories" table, and ALL the info from the "details" table where the "catId" matches, could it be written as such: SELECT catName, * ?)

    FROM categories as c, details as d (This sets up aliases to reduce typing in the query?)

    WHERE c.catId = d.catId AND c.catId = 1 (This is matching the tables using the PK of "categories" and setting the condition for the SELECT to extract only the information that matchs c.catId = 1, right?)
    Last edited by PHP John; Aug 28, 2002 at 15:12.
    John

  7. #7
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1) The order does not make a difference. Notice I didn't use the aliases here because there are no duplicate names in the table, apart from catId (I used the aliases in the WHERE clause). I could have done this:

    c.catName, d.make, d.model

    If you want to select everything from one table but selective items from the other use the alias for the *:

    catName, d.*

    2) Yep. You don't need to, you could use:

    catagories.catName, details.make, details.model

    3) Yep. The first clause is used to match the items in the tables and the second is just a standard WHERE clause

    BTW - I'll move this to a more appropriate forum

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  8. #8
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah!

    Thanks for the explaination. It's going to take some time and repetition to digest (As all things do with me )

    Thanks again.
    John


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
  •