Stuck with Tricky SQL query - pls help

[mattis2k]

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..

[tamilselvan]

SELECT *
FROM search_groups
WHERE category_id IN ( 51, 135, 136 );

Is this are you looking for?

Tamil

[rigatoni]

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.