SQL statement for Search Page

[Bosnoval]

(Windows 2000 Server SP4 running ASP web pages with a Microsoft Access database)

Hi, I’m a newb just trying to get some help with an Access database SQL statement…

I’ve been trying to add a feature to an existing search page but I’m not having much luck with modifying the SQL statement to get it to work. The existing webpage searches a photo album database. You can search by people (keywords) and categories (places, events, trips, etc). All I’m trying to do is make it to where the album can be optionally searched by a pair of people / keywords (i.e. Find all photos with Bob & Sue in them).

To do this I’ve added a new menu with the name of “mnu_keywordList2” which has the exact values available of the original “mnu_keywordList” and default values of “%” which means the menu was left at a default setting of “Any”.

Here’s the existing SQL statement (simplified):

SELECT DISTINCT Pictures.PicRegDate, Pictures.PicFilename, Pictures.PicId, Pictures.PicDate,  edit AS [ThumbWebPath],      edit AS [ImageAltTag], edit AS [WebSourceImgPath]
 
FROM (Pictures  LEFT JOIN Attributes ON Pictures.PicId=Attributes.PicId)  LEFT JOIN KeyWords ON Pictures.PicId=KeyWords.PicId
 
WHERE (Attributes.Data LIKE 'mnu_ValueList' Or ('mnu_ValueList'='%' And Attributes.Data Is Null)) AND ((KeyWords.Keyword LIKE 'mnu_keywordList') Or ('mnu_keywordList'='%' AND KeyWords.Keyword Is Null))   AND (YEAR(Pictures.PicDate) BETWEEN 'txtDateRngBegin' AND 'txtDateRngEnd')
 
ORDER BY Pictures.PicDate
 

At first I thought this was going to be a simple change of the WHERE clause and tried variations on the WHERE statement to the effect of:

WHERE (Attributes.Data LIKE 'mnu_ValueList' Or ('mnu_ValueList'='%' And Attributes.Data Is Null)) AND ((KeyWords.Keyword LIKE 'mnu_keywordList' AND 'mnu_keywordList2') Or (('mnu_keywordList'='%' AND 'mnu_keywordList2'='%') AND KeyWords.Keyword Is Null))   AND (YEAR(Pictures.PicDate) BETWEEN 'txtDateRngBegin' AND 'txtDateRngEnd')
 

Now I’m guessing the table formed is going to have to be changed but wanted to make sure before I from scratch. In any case, I’d appreciate any help I can get!

[NeoPa]

Nicely posted question - I wish they were all as clearly defined.
Firstly, I don't understand the 'edit AS ...' stuff but I'll comment on parts of the WHERE clauses.
Specifically the LIKE construct.
LIKE expects a wildcard in the comparison which seems to be missing from your code.
In Access it uses '*'; In MS SQL it uses '&' (I think) and in Excel (MS Query) it expects '%'.
So, for instance, in Access

WHERE (Attributes.Data LIKE 'mnu_ValueList' Or ...

should be something like

WHERE (Attributes.Data LIKE 'BlahBlah*' Or ...

or

WHERE (Attributes.Data LIKE '*BlahBlah*' Or ...

I've also just noticed that the SQL doesn't seem to have the resolved value in the comparison.
I don't know how you create this SQL, so I can't post a correction, but this seems to be comparing the data against a string which is the name of the variable, rather than against the contents of it.

I hope this helps - repost where you understand to if you still have problems.

[Bosnoval]

Nicely posted question - I wish they were all as clearly defined.
Firstly, I don't understand the 'edit AS ...' stuff but I'll comment on parts of the WHERE clauses.

Ha-ha, thanks. The edits are just where I was removing some long unrelated static information that got concatenated to form the ThumbWebPath and ImageAltTag variables for images on the page (stuff like filenames and directory path info).

Specifically the LIKE construct. LIKE expects a wildcard in the comparison which seems to be missing from your code. In Access it uses '*'; In MS SQL it uses '&' (I think) and in Excel (MS Query) it expects '%'.

I guess the LIKE calls in this are just bad form but all they are really doing are string comparisons. For instance:

'mnu_keywordList' might be the user selected value, “Doe, John” and it’s trying to match that to a value listed in KeyWords.Keyword. All of this works fine. It didn’t fail until I started editing ha-ha. I was just trying to make it look for the two keywords as a pair; if the user selects that option.

[Bosnoval]

The picture above is how the search page would look. All selections are optional but this particular search would result in all pictures of "Adams, Jane" and "Adams, John" together during Christmas.

I'm just asking how to accomplish this. The way it's setup now you can't search for a pair of people / keywords, only one person / keyword. Is it possible to accomplish this via a WHERE clause or does the entire statement have to be rewritten?

[Bosnoval]

I've also just noticed that the SQL doesn't seem to have the resolved value in the comparison.
I don't know how you create this SQL, so I can't post a correction, but this seems to be comparing the data against a string which is the name of the variable, rather than against the contents of it.

To answer this do you mean what value do I expect to be returned in the end? Meaning the final returned value from a user’s search? If so, that value would be a table of rows that have the matching criteria. Those rows would contain image data that a “results ASP page” will use to display the matching images, most of the time, via thumbnail images in a 4 x 3 grid layout. From there the resulting thumbnail images are loaded with all sorts of JavaScript to pull up more detailed descriptions and links to full size images, etc. It’s really just a searchable photo album.

[NeoPa]

This is probably academic as you say the SQL works for you fundamentally, but no, I was commenting on the SQL you have posted above.
From my reading of it - it will not compare the recordset data with the value in 'mnu_ValueList' (as one example) as I would assume you intend, but rather with the actual string "mnu_ValueList" itself.

[Bosnoval]

This is probably academic as you say the SQL works for you fundamentally, but no, I was commenting on the SQL you have posted above.
From my reading of it - it will not compare the recordset data with the value in 'mnu_ValueList' (as one example) as I would assume you intend, but rather with the actual string "mnu_ValueList" itself.

Ah, there’s a string request before the SQL statement to handle that. It works. It does an error check and sets a default in case the variable isn’t returned from the form for whatever reason.

i.e. mnu_keywordList = Request.QueryString("mnu_keywordList")

[Bosnoval]

I think we’re just getting caught up on form & semantics. I’ll worry about that later. I’m just after the best approach to the problem at hand.

------------------------

There are three tables being referenced in this call. Each table has a common element and that’s the PicId. Every photo has a unique PicId. ...and there are about 14,000 PicId's at the moment.

Pictures Table Data
This is the most obvious data. Each row is a different picture with a unique PicId. For every row / PicID there is picture data such as Filename, Description, PicDate, Registered Date (date entered into database), PicSource and a thumbnail image, etc.

KeyWords Table Data
This table is a listing of every keyword that applies to any given photo. In this case the keywords are people’s name. There will be a separate line for every keyword and so there can be multiple lines with the same PicID. That’s because photos can have one or more keyword / people pictured in them.

Attributes Table Data
This table is laid out in the same form as the KeyWords table except that instead of tracking keywords / people it’s tracking broad categories such as Events, Holidays, Special Occasions, trip names, etc.

[NeoPa]

I'm not sure that I can help you Bosnoval.
If I see something I think might cause problems then I post.
Or if I see a (generally straightforward) solution to a problem also.
That bit of the SQL I mentioned is what I thought I could help with...
Sorry.