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

"

"

X vBulletin 4.2.2 Debug Information

  • Page Generation 0.15478 seconds
  • Memory Usage 2,868KB
  • Queries Executed 13 (?)
More Information
Template Usage (34):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)bbcode_code
  • (1)bbcode_php
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (4)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (1)navbar_moderation
  • (1)navbar_noticebit
  • (1)navbar_tabs
  • (2)option
  • (4)postbit
  • (4)postbit_onlinestatus
  • (4)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (26):
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/functions_navigation.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_facebook.php
  • ./includes/functions_calendar.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (71):
  • init_startup
  • friendlyurl_resolve_class
  • init_startup_session_setup_start
  • database_pre_fetch_array
  • database_post_fetch_array
  • init_startup_session_setup_complete
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • load_show_variables
  • load_forum_show_variables
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • strip_bbcode
  • friendlyurl_clean_fragment
  • friendlyurl_geturl
  • forumjump
  • cache_templates
  • cache_templates_process
  • template_register_var
  • template_render_output
  • fetch_template_start
  • fetch_template_complete
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_img_match
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • build_navigation_data
  • build_navigation_array
  • check_navigation_permission
  • process_navigation_links_start
  • process_navigation_links_complete
  • set_navigation_menu_element
  • build_navigation_menudata
  • build_navigation_listdata
  • build_navigation_list
  • set_navigation_tab_main
  • set_navigation_tab_fallback
  • navigation_tab_complete
  • fb_like_button
  • showthread_complete
  • page_templates