-
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 06:13 AM.
-
SELECT *
FROM search_groups
WHERE category_id IN ( 51, 135, 136 );
Is this are you looking for?
Tamil
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|