reducing search string

[reubenb]

hi,
i have this nice and big SQL search string that will search from some tables.

how can i make it so it will release the stress from the sql server each time it does this?
and, how do i get the WHERE articles_level >= " & author_status to work
so that if the articlel_level 3, and the author_status is 2, it wont get it.


strSQL = "SELECT articles_desc, articles_title, type, articles_id, articles_keywords, articles_level FROM articles WHERE articles_level >= " & author_status & " AND articles_desc LIKE '%" & Replace(strSearch, "'", "''") & "%' OR articles_title LIKE '%" & Replace(strSearch, "'", "''") & "%' OR articles_keywords LIKE '%" & Replace(strSearch, "'", "''") & "%' UNION SELECT songs_desc, songs_title, type, songs_id, songs_keywords, songs_level FROM songs WHERE songs_level >= " & author_status & " AND songs_desc LIKE '%" & Replace(strSearch, "'", "''") & "%' OR songs_title LIKE '%" & Replace(strSearch, "'", "''") & "%' OR songs_keywords LIKE '%" & Replace(strSearch, "'", "''") & "%'UNION SELECT images_desc, images_title, type, images_id, images_keywords, images_level FROM images WHERE images_level >= " & author_status & " AND images_desc LIKE '%" & Replace(strSearch, "'", "''") & "%' OR images_title LIKE '%" & Replace(strSearch, "'", "''") & "%' OR images_keywords LIKE '%" & Replace(strSearch, "'", "''") & "%' UNION SELECT videos_desc, videos_title, type, videos_id, videos_keywords, videos_level FROM videos WHERE videos_level >= " & author_status & " AND videos_desc LIKE '%" & Replace(strSearch, "'", "''") & "%' OR videos_title LIKE '%" & Replace(strSearch, "'", "''") & "%' OR videos_keywords LIKE '%" & Replace(strSearch, "'", "''") & "%' UNION SELECT weblinks_desc, weblinks_title, type, weblinks_id, weblinks_keywords, weblinks_level FROM weblinks WHERE weblinks_level >= " & author_status & " AND weblinks_desc LIKE '%" & Replace(strSearch, "'", "''") & "%' OR weblinks_title LIKE '%" & Replace(strSearch, "'", "''") & "%' OR weblinks_keywords LIKE '%" & Replace(strSearch, "'", "''") & "%' UNION SELECT peulot_desc, peulot_title, type, peulot_id, peulot_keywords, peulot_level FROM peulot WHERE peulot_level >= " & author_status & " AND peulot_desc LIKE '%" & Replace(strSearch, "'", "''") & "%' OR peulot_title LIKE '%" & Replace(strSearch, "'", "''") & "%' OR peulot_keywords LIKE '%" & Replace(strSearch, "'", "''") & "%' UNION SELECT quotations_quotation, quotations_author, type, quotations_id, quotations_keywords, quotations_level FROM quotations WHERE quotations_level >= " & author_status & " AND quotations_quotation LIKE '%" & Replace(strSearch, "'", "''") & "%' OR quotations_author LIKE '%" & Replace(strSearch, "'", "''") & "%' OR quotations_keywords LIKE '%" & Replace(strSearch, "'", "''") & "%' UNION SELECT generalchinuch_desc, generalchinuch_title, type, generalchinuch_id, generalchinuch_keywords, generalchinuch_level FROM generalchinuch WHERE generalchinuch_level >= " & author_status & " AND generalchinuch_desc LIKE '%" & Replace(strSearch, "'", "''") & "%' OR generalchinuch_title LIKE '%" & Replace(strSearch, "'", "''") & "%' OR generalchinuch_keywords LIKE '%" & Replace(strSearch, "'", "''") & "%' UNION SELECT keythemesquestions_keythemesquestions, keythemesquestions_desc, type, keythemesquestions_id, keythemesquestions_keywords, keythemesquestions_level FROM keythemesquestions WHERE keythemesquestions_level >= " & author_status & " AND keythemesquestions_keythemesquestions LIKE '%" & Replace(strSearch, "'", "''") & "%' OR keythemesquestions_desc LIKE '%" & Replace(strSearch, "'", "''") & "%' OR keythemesquestions_keywords LIKE '%" & Replace(strSearch, "'", "''") & "%' UNION SELECT relatedtopics_url, relatedtopics_topic, type, relatedtopics_id, relatedtopics_keywords, relatedtopics_level FROM relatedtopics WHERE relatedtopics_level >= " & author_status & " AND relatedtopics_topic LIKE '%" & Replace(strSearch, "'", "''") & "%' OR relatedtopics_url LIKE '%" & Replace(strSearch, "'", "''") & "%' OR relatedtopics_keywords LIKE '%" & Replace(strSearch, "'", "''") & "%' UNION SELECT programideas_programideas, programideas_desc, type, programideas_id, programideas_keywords, programideas_level FROM programideas WHERE programideas_level >= "& author_status & " AND programideas_programideas LIKE '%" & Replace(strSearch, "'", "''") & "%' OR programideas_desc LIKE '%" & Replace(strSearch, "'", "''") & "%' OR programideas_keywords LIKE '%" & Replace(strSearch, "'", "''") & "%'"

[Morgoth]

Post examples of what this search filter is suppose to filter so I can read it easier. I don't have time to figure it all out in one sitting, I have lots of work to do, but I want to help you.

[reubenb]

OK. Thanks.

So, there is 12 tables that it has to search and get all the fields but then use a WHERE field >= SessionLevel, where field is different name for each table.

e.g.,
Each user has a different level (1, 2, 3, 4)
and each item has a different level (1, 2, 3, 4)
so they cant search for items that aren't their level.

[reubenb]

anyone have any idea?