www.webdeveloper.com
Results 1 to 4 of 4

Thread: joining multiple tables

  1. #1
    Join Date
    Aug 2004
    Location
    Brighton,UK
    Posts
    96

    joining multiple tables

    i am having a problem with trying to join 4 tables

    i am trying to show a list of images for product type

    i have category table that is shorted to "<b>cat</b>" that contains categories like "rings", "ear rings", "bracelets", "winter collection", "summer collection"

    then i have a table called "<b>links</b>" so i can link the categories to items that are in the "<b>items</b>" table, the resion i have the links table is because an item can be in 2 or more categories an individual ear ring will be in the "ear ring" category but also "winter collection" or "summer collection".

    then there is a media table witch contains the names of the images linked to an item, some will have one image some will have many so once i have my list of items i need be able to pick just one of the images by only selecting an image with "ord" (order) that is 0, the id in the items table links to link_id in the media table

    so basically i want to select a all the items from one of the categories then get a list of images that relate to them. at the moment my SQL looks like this.

    SELECT * FROM cat
    JOIN links ON cat.id = links.cat_id
    JOIN items ON items.id = links.item_id
    JOIN media ON media.link_id = links.id
    WHERE cat.id = 6 AND media.ord = 0
    ORDER BY links.ord ASC

    at the moment this works but it dose not let me get at values in the other tables link media.file_name or items.name_html

    i have only just started to JOIN staments and getting really confused with the syntax and i still have no idea what a LEFT or RIGHT join is meant for!

    this is my data base design in case what i have written is a little confusing, you can see what i am trying to join in the purple hope this helps to make more sense:


  2. #2
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    To get the other columns, try something like this:

    Code:
    SELECT c.*, l.*, i.*, m.* FROM cat c
    JOIN links l ON cat.id = links.cat_id
    JOIN items i ON items.id = links.item_id
    JOIN media m ON media.link_id = links.id
    WHERE cat.id = 6 AND media.ord = 0
    ORDER BY links.ord ASC
    You may need to use column aliases if columns in multiple tables are repeated by name.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  3. #3
    Join Date
    Aug 2004
    Location
    Brighton,UK
    Posts
    96
    thats grate but when i ran it i got an:
    Unknown column 'cat.id' in 'where clause'

    so i swapped tables names for there aliases:

    SELECT c.*, l.*, i.*, m.* FROM cat c
    JOIN links l ON c.id = l.cat_id
    JOIN items i ON i.id = l.item_id
    JOIN media m ON m.link_id = l.id
    WHERE c.id = 6 AND m.ord = 0
    ORDER BY l.ord ASC

    my only problem now is that when i run it in php i can't get at the field values. if look for "id" i get the id of the cat table i have tried cat.id and c.id and nothing works.

    PHP Code:

            $sql 
    "SELECT c.*, l.* , m.*, m.* FROM cat c ";
        
    $sql .= "JOIN links l ON c.id = l.cat_id ";
        
    $sql .= "JOIN items i ON i.id = l.item_id ";
        
    $sql .= "JOIN media m ON m.link_id = l.id ";
        
    $sql .= "WHERE c.id = 6 AND m.ord = 0 ";
        
    $sql .= "ORDER BY l.ord ASC";

        
    $result mysql_query($sql);
        
        if (
    $result){
            while(
    $row mysql_fetch_assoc($result)){
                echo 
    "id = {$row['id']}, ";
                echo 
    "c.id = {$row['c.id']}, ";
                echo 
    "m.id = {$row['m.id']}<br />\n";
            }
        }else{
            echo 
    mysql_error()."\n<br>";
        } 
    out puts:
    id = 81, c.id = , m.id =
    id = 51, c.id = , m.id =
    id = 103, c.id = , m.id =

    thanks for the help

  4. #4
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    like i said try using aliases on your columns
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles