www.webdeveloper.com
Results 1 to 2 of 2

Thread: [RESOLVED] SQL Column MUST match all values

  1. #1
    Join Date
    Sep 2004
    Location
    At the corner of WALK and DONT WALK
    Posts
    1,834

    resolved [RESOLVED] SQL Column MUST match all values

    Okay, I've got a bit of a brainfart here. This is the SQL statement, which is returning 0 lines.

    PHP Code:
    SELECT
        
    `ds_lines`.`set_num`,
        `
    ds_lines`.`line_num`,
        `
    ds_lines`.`line_desc`
        
    FROM `ds_lines`
            
    JOIN `ds_line_setsON  `ds_lines`.`set_num` = `ds_line_sets`.`set_num`
            
    JOIN `ds_line_categsON `ds_lines`.`set_num` = `ds_line_categs`.`set_num`
                AND `
    ds_lines`.`line_num` = `ds_line_categs`.`line_num`
        
    WHERE `ds_line_categs`.`categ_id` = "Furres"
            
    AND `ds_line_categs`.`categ_id` = "Items"
            
    AND `ds_line_categs`.`categ_id` = "Locations_Relative"



    What I need it to do is find all records in the table `ds_lines` where there is a corresponding entry for all three categ_id values in `ds_line_categs`.

    For example, in ds_lines, I have the entry:

    Code:
    +---------+----------+---------------------------------+
    | set_num | line_num | line_desc                       |
    +---------+----------+---------------------------------+
    |       0 |        3 | When someone moves into item #, |
    +---------+----------+---------------------------------+
    In ds_line_categs, the entries for 0,3 are:
    Code:
    +---------+----------+------------------------------+
    | set_num | line_num | categ_id                     |
    +---------+----------+------------------------------+
    |       0 |        3 | Furres                       |
    |       0 |        3 | Furres_Actions               |
    |       0 |        3 | Furres_Actions_Move          |
    |       0 |        3 | Furres_Select                |
    |       0 |        3 | Furres_Select_Triggering     |
    |       0 |        3 | Items                        |
    |       0 |        3 | Items_Furres                 |
    |       0 |        3 | Items_Number                 |
    |       0 |        3 | Locations                    |
    |       0 |        3 | Locations_Relative           |
    |       0 |        3 | Locations_Relative_FurreMove |
    +---------+----------+------------------------------+
    As you can see by the red entries, there is a corresponding entry for that set/line number pair and all three categ_id's. This is a line I want returned. I do NOT want the line 0:2, which is:
    Code:
    +---------+----------+----------------------------------+
    | set_num | line_num | line_desc                        |
    +---------+----------+----------------------------------+
    |       0 |        2 | When someone moves into floor #, |
    +---------+----------+----------------------------------+
    Its entries in `ds_line_categs` are:
    Code:
    +---------+----------+------------------------------+
    | set_num | line_num | categ_id                     |
    +---------+----------+------------------------------+
    |       0 |        2 | Floors                       |
    |       0 |        2 | Floors_Number                |
    |       0 |        2 | Furres                       |
    |       0 |        2 | Furres_Actions               |
    |       0 |        2 | Furres_Actions_Move          |
    |       0 |        2 | Furres_Select                |
    |       0 |        2 | Furres_Select_Triggering     |
    |       0 |        2 | Locations                    |
    |       0 |        2 | Locations_Relative           |
    |       0 |        2 | Locations_Relative_FurreMove |
    +---------+----------+------------------------------+
    As you can see, while it has the categ_id "Furre" and categ_id "Locations_Relative", the categ_id "Item" is not there. Thus, this line is to be omitted from the results because it is missing one of the require categ_id values.

    And if I use "OR" in my WHERE statement, it will match all lines that have any one of those values, and that won't do either (I get the result with the IN method). I only want the ones that have all three.

    How do I do this?
    Last edited by Mr Initial Man; Yesterday at 02:33 AM.

  2. #2
    Join Date
    Sep 2004
    Location
    At the corner of WALK and DONT WALK
    Posts
    1,834
    Never Mind. I found how I did it originally:

    PHP Code:
    SELECT
        
    `ds_lines`.`set_num`,
        `
    ds_lines`.`line_num`,
        `
    ds_lines`.`line_desc`
        
    FROM `ds_lines`
            
    JOIN `ds_line_setsON  `ds_lines`.`set_num` = `ds_line_sets`.`set_num`
            
    JOIN `ds_line_categsON `ds_lines`.`set_num` = `ds_line_categs`.`set_num`
                AND `
    ds_lines`.`line_num` = `ds_line_categs`.`line_num`
        
    JOIN `ds_line_categs` AS `dslc0ON `dslc0`.`categ_id` = "Furres"
            
    AND `dslc0`.`set_num` = `ds_lines`.`set_num`
            AND `
    dslc0`.`line_num` = `ds_lines`.`line_num`
        
    JOIN `ds_line_categs` AS `dslc1ON `dslc1`.`categ_id` = "Items"
            
    AND `dslc1`.`set_num` = `ds_lines`.`set_num`
            AND `
    dslc1`.`line_num` = `ds_lines`.`line_num`
        
    JOIN `ds_line_categs` AS `dslc2ON `dslc2`.`categ_id` = "Locations_Relative"
            
    AND `dslc2`.`set_num` = `ds_lines`.`set_num`
            AND `
    dslc2`.`line_num` = `ds_lines`.`line_num`



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.10571 seconds
  • Memory Usage 2,883KB
  • Queries Executed 15 (?)
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
  • (4)bbcode_code
  • (2)bbcode_php
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (2)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (1)navbar_moderation
  • (1)navbar_noticebit
  • (1)navbar_tabs
  • (2)option
  • (2)postbit
  • (2)postbit_onlinestatus
  • (2)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 (70):
  • 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
  • bbcode_parse_start
  • postbit_imicons
  • bbcode_parse_complete_precache
  • 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