searching keywords sql

[kumiko]

halo there, i have some sql query question need to ask.i know maybe this is quite easy for others.but i really cant figure out how to query this thing i wan since i m quite poor in database sql .
hereby i have attached an image.


first, user can insert a set of string keywords to search --> apple, orange, pear. there are 3 tables involve. by default, if user didnt check the radio button, the system will search the result from table A which consist of( photoid, photoname and photokeywords). it will match the keywords and then display out the photo name as result.
my problem is..when the user CLICK on the radio button, the system still will do the searching same as below + do another matching to search the LKeywords in the table B which consist of( lightboxid, lightboxname, and LKeywords).

if it is match, then will go to table C(lgihtboxphoptoid,lightboxid, photoid) to see tat lightbox id tat get jz now got any photos id related or not . if ther eis the photoid in the table C, then get back the photo name from the table A.

i am using ASP with MS SQL.

i realy headache to get out this result. can someone plz help me? i need help. urgent

[wige]

So I take it the issue you are having is that the search is being too "greedy" - it is not confining it's search to the photokeywords field in table 1, it is also getting results from Lkeyword in table 2?

If that is the case, I would suggest checking how the database is set up. Some setups create a keyword index to optimize text searching. If this index combines both keyword fields, the problem could occur. You would need to set up a second keyword index for the second table. Unfortunately I am not quite familiar enough with MS SQL to advise further.

[jimn]

Kumiko,

It is impossible to debug your problem without knowing what, exactly you are doing in your script and SQL query. There may be logic errors in your script code and/or in your SQL query.

On another issue, when you build SQL queries on the fly, you must be very careful to protect your data from an SQL Injection attack. See the following article:
SQL Injection Attacks - Are You Safe?

[jtracking]

if you could show us the sql and i presume you want your sql to know if the radio box was selected correct???

show me the sql

[tim2005]

Hi,

The simplest implementation of your task requires two queries:

1) query for search by photo keyword only
SELECT A.Photoname FROM A
WHERE A.photokeywords LIKE Search_Keyword

2) query for search by photo keyword and lightbox keyword
SELECT A.Photoname FROM
C INNER JOIN A ON C.Photoid = A.Photoid
INNER JOIN B ON C.Ligghtboxid = B.Lightboxid
WHERE A.photokeywords LIKE Search_Keyword OR B.Lkeywords LIKE Search_Keyword

If you need search keyword contained in both tables you should change OR to AND in WHERE clause:
WHERE A.photokeywords LIKE Search_Keyword AND B.Lkeywords LIKE Search_Keyword

This implementation shows the main principle and will not give the best performance. Note please that in examples above omitted keywords preparation. Search performed by one keyword only.

The exact implementation depends on MS SQL version, configuration and available features.

Better performance and flexibility will be achieved with full-text querying support.

If you have not mach data and queries number, you can use LIKE.

[John Redfield]

Its difficult to understand exactly what you want to happen. More detail is needed.

But from what I can gather from your post, you want the photoname included in the result from table C.

If the query joins are in place for the three tables, simply add the "photoname" to the Select Statement.

John