Stuck with Tricky SQL query - pls help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Stuck with Tricky SQL query - pls help

  1. #1
    Join Date
    Jan 2006
    Posts
    1

    Stuck with Tricky SQL query - pls help

    Hi,

    I have a question about a query I'm working on.

    I have a search form which allows for advanced searching of some data.

    The search is for "resources", you can search on "title" which is free text.

    and then there are some check boxes on the page which allow to search on "Band", "Skill" and "level"

    Heres the tables

    [RESOURCE]
    resource_id,
    resource_type_id,
    unique_id,
    company_id,
    resource_title,
    comment,
    status,
    created_date,
    cancelled,
    cancelled_date,
    cancellation_reason,
    company_address_id,
    learning_provider_id,
    program_only,
    patents,

    [SEARCH_GROUPS]

    search_group_id,
    category_id,
    parent_id,
    search_group_title,
    z_order_id

    [RESOURCE_SEARCH_GROUPS]
    resource_search_group_id
    resource_id
    resource_type_id
    search_group_id
    z_order_id

    Now to obtain the bands, skills and levels the follow queries are needed

    SELECT * FROM search_groups WHERE category_id=
    [135 returns bands]
    [51 returns skills]
    [136 returns levels]

    Now Im a novice with Oracle SQL and other SQL. So was looking for a little help to allow to search for resources based on the title field and where the bands, skills and levels are within the checkbox values they selected

    Any help would be great..
    Last edited by mattis2k; 01-26-2006 at 05:13 AM.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    SELECT *
    FROM search_groups
    WHERE category_id IN ( 51, 135, 136 );

    Is this are you looking for?

    Tamil

  3. #3
    Join Date
    Nov 2003
    Location
    Ohio
    Posts
    51
    You can add the title search into Tamil's query in a couple of
    ways:

    1. Title specifically matches something:

    SELECT *
    FROM search_groups
    WHERE category_id IN ( 51, 135, 136 )
    AND resource_title = '????';


    2. Title contains some string:

    Begins with it:

    SELECT *
    FROM search_groups
    WHERE category_id IN ( 51, 135, 136 )
    AND resource_title LIKE '%';


    Contains it:

    SELECT *
    FROM search_groups
    WHERE category_id IN ( 51, 135, 136 )
    AND resource_title LIKE '%%';

    Ends with it:

    SELECT *
    FROM search_groups
    WHERE category_id IN ( 51, 135, 136 )
    AND resource_title LIKE '%';

    You could also use the INSTR function or depending on your version you can use Oracle Text (check out CTXCAT in version 9 and up). You may also have to deal with multiple keywords on your search.
    ____________________
    Pete

Posting Permissions

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



Click Here to Expand Forum to Full Width