Search query

[Bosnoval]

Hello, I'm trying to find a solution to a simple search engine's SQL query problem. At the moment my query has a flaw that makes it leave out valid records if the user selects "Any" from a dynamic option (drop down list) menu instead of actually selecting an individual statement. I assigned the "Any" selection to have the value of '%'.

************* Start summary of HTML end *************
The gist of the html end of the search engine goes like this:

[ Drop List 1 ]

[ Drop List 2 ] [ Drop List 3 ]

The search is on a database of cataloged images. The result of the search should be a table of images that match the user requested parameters.

Drop List 1 is simply a list of all people shown in the pictures. Its default value is "Any" with a real passed value of '%'. An actual select example other than "Any" could be "Doe, John". Drop List 1 values are in the database as "KeyWords.Keyword".

Drop List 2 and 3 are simply organizational categories the images may fall under. Drop List 2 is the parent menu of Drop List 3. Changing Drop List 2 dynamically changes Drop List 3. The default for these menus again are "Any" with a real passed value of '%'. An actual select example other than "Any" could be: for Drop List 2, "Event" and for Drop List 3, "Weddings". Drop List 3 values are in the database as "Attributes.Data".

The search result should then be any images of John Doe at a wedding.

(There's also a date range but that part isn't the problem so leaving it out.)
************* END summary of HTML end *************


************* Start My current SQL query *************

SELECT DISTINCT Pictures.PicFilename, Pictures.PicId, Pictures.PicDate

...truncated statement

FROM Pictures, Attributes, KeyWords
WHERE (Attributes.Data LIKE 'mnu_ValueList') AND KeyWords.Keyword LIKE 'mnu_keywordList' AND (Attributes.PicId = Pictures.PicId) AND (Attributes.PicId = KeyWords.PicId) AND (YEAR(Pictures.PicDate) BETWEEN 'txtDateRngBegin' AND 'txtDateRngEnd')
ORDER BY Pictures.PicDate

************* END My current SQL query *************



Problem: My problem stems from the fact that it's possible for an image to be in the database that does not have any related keyword (person) or attribute data (category or event). One example that fails is a picture of a person with no related "Event" (Attribues.Data). In this case my WHERE statement causes the picture to be omitted because the image does not even exist in the Attribute table. The user selection of "Any" passes the value of '%' While this does produce the expected results if the picture just happens to be in the Attribute table it however fails if it is not.

"Any" causes (Attributes.Data LIKE 'mnu_ValueList') to be
(Attributes.Data LIKE '%'). This selects all entries in the Attributes table but since the picture doesn't have a related entry in the Attributes table the "AND" statement causes the image to be omitted.

Is there a way to modify the SQL statement to fix this?

 

[PHV]

Take a look at outer joins:
FROM Pictures LEFT JOIN (
Attributes LEFT JOIN KeyWords ON Attributes.PicId=KeyWords.PicId
) ON Pictures.PicId=Attributes.PicId

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Bosnoval]

Thank you for your help.

When using JOIN in this way is it still necessary to have:

Attributes.PicId = Pictures.PicId
Attributes.PicId = KeyWords.PicId

in the WHERE clause? The results using the LEFT JOIN as you specified below has already helped by including records that were being left out before but is still not giving a complete listing of records. I'm not immediately seeing the pattern for the omitted records.



************* Start My current SQL query *************

SELECT DISTINCT Pictures.PicFilename, Pictures.PicId, Pictures.PicDate

...truncated statement

FROM Pictures LEFT JOIN (
Attributes LEFT JOIN KeyWords ON Attributes.PicId=KeyWords.PicId
) ON Pictures.PicId=Attributes.PicId

WHERE (Attributes.Data LIKE 'mnu_ValueList') AND KeyWords.Keyword LIKE 'mnu_keywordList' AND (Attributes.PicId = Pictures.PicId) AND (Attributes.PicId = KeyWords.PicId) AND (YEAR(Pictures.PicDate) BETWEEN 'txtDateRngBegin' AND 'txtDateRngEnd')

ORDER BY Pictures.PicDate

************* END My current SQL query *************

 

[PHV]

Get rid of the following conditions in your WHERE clause:
AND (Attributes.PicId = Pictures.PicId) AND (Attributes.PicId = KeyWords.PicId)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Bosnoval]

Well, I just realized the test case I was using to test the modified SQL statement wasn't quite right. It turns out the before and after SQL statements are actually producing the same results afterall. All of the same records are still being omitted.

I believe I've read somewhere that using statements like my original:

Attributes.PicId = Pictures.PicId AND
Attributes.PicId = KeyWords.PicId

in the WHERE field is an equivalent syntax to the LEFT join you suggested. I think we are maybe using two different syntax to produce the same results???



************* Start My current SQL query *************

SELECT DISTINCT Pictures.PicFilename, Pictures.PicId, Pictures.PicDate

...truncated statement

FROM Pictures LEFT JOIN ( Attributes LEFT JOIN KeyWords ON Attributes.PicId=KeyWords.PicId ) ON Pictures.PicId=Attributes.PicId

WHERE (Attributes.Data LIKE 'mnu_ValueList') AND KeyWords.Keyword LIKE 'mnu_keywordList' AND (YEAR(Pictures.PicDate) BETWEEN 'txtDateRngBegin' AND 'txtDateRngEnd')

ORDER BY Pictures.PicDate

************* END My current SQL query *************

 

[PHV]

Replace this:
WHERE (Attributes.Data LIKE 'mnu_ValueList') AND KeyWords.Keyword LIKE 'mnu_keywordList'
By this:
WHERE (Attributes.Data LIKE 'mnu_ValueList' Or Attributes.Data Is Null) AND (KeyWords.Keyword LIKE 'mnu_keywordList' Or KeyWords.Keyword Is Null)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Bosnoval]

Thank you again, your swift replies are much appreciated!

This produced an unexpected result though. The resulting table contains a lot of mismatched records.


************* Start My current SQL query *************

SELECT DISTINCT Pictures.PicFilename, Pictures.PicId, Pictures.PicDate

...truncated statement


FROM Pictures LEFT JOIN ( Attributes LEFT JOIN KeyWords ON Attributes.PicId=KeyWords.PicId ) ON Pictures.PicId=Attributes.PicId

WHERE (Attributes.Data LIKE 'mnu_ValueList' Or Attributes.Data Is Null) AND (KeyWords.Keyword LIKE 'mnu_keywordList' Or KeyWords.Keyword Is Null)


AND (YEAR(Pictures.PicDate) BETWEEN 'txtDateRngBegin' AND 'txtDateRngEnd')
ORDER BY Pictures.PicDate

************* END My current SQL query *************

 

[PHV]

Sorry, my bad:
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)

I don't know the NZ equivalent function for your RDBMS.
In pure access:
WHERE Nz(Attributes.Data) LIKE 'mnu_ValueList' AND Nz(KeyWords.Keyword) LIKE 'mnu_keywordList'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

Sorry, my bad:
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))

I don't know the NZ equivalent function for your RDBMS.
In pure access:
WHERE Nz(Attributes.Data) LIKE 'mnu_ValueList' AND Nz(KeyWords.Keyword) LIKE 'mnu_keywordList'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Bosnoval]

This is just running using a DSN on a Windows 2000 server. SQL server, etc. is not installed. The "Is Null" statements should work but the result is still the same as my original SQL statement.

The LEFT JOIN simply adds entries from the Attributes or KeyWords table that match the given parameters. Wouldn't the pictures that are being omitted be left out because they actually exist outside of the created/combined table because they do not have entires in either the Attributes or KeyWords table? It seems like we're trying to get a match on an entry we know doesn't exist. Is there a way to simply omit the entire "Attributes.Data LIKE 'mnu_ValueList'" SQL query segment if 'mnu_ValueList' = '%'? Afterall, shouldn't running the query without that line produce the results that selecting "Any" for Attributes.Data should?

 

[Bosnoval]

Maybe I am having a Null/field comparison issue here. Running this below also failed to give a complete listing of records.

*****************************
SELECT DISTINCT Pictures.PicFilename, Pictures.PicId, Pictures.PicDate

...truncated statement

FROM Pictures LEFT JOIN ( Attributes LEFT JOIN KeyWords ON Attributes.PicId=KeyWords.PicId ) ON Pictures.PicId=Attributes.PicId



WHERE KeyWords.Keyword LIKE 'mnu_keywordList'


AND (YEAR(Pictures.PicDate) BETWEEN 'txtDateRngBegin' AND 'txtDateRngEnd')
ORDER BY Pictures.PicDate
*****************************

 

[Bosnoval]

Looking over the results with the simplified SQL statement the pictures omitted are exactly the records that lack a Attributes.Data field.

Doesn't this mean the JOIN is somehow responsible? The JOIN in on the PicId.

The PicId for the omitted records would only exist in the Pictures table since they don't have Attributes or KeyWords. Could linking the JOIN on the PicId somehow remove the entries since those images don't have a PicId in all three tables?

 

[Bosnoval]

My problem is definately the JOIN unfortunately I don't know how to fix it. On a hunch I inverted your statement from:

FROM Pictures LEFT JOIN ( Attributes LEFT JOIN KeyWords ON Attributes.PicId=KeyWords.PicId ) ON Pictures.PicId=Attributes.PicId

to:

FROM Pictures LEFT JOIN ( KeyWords LEFT JOIN Attributes ON KeyWords.PicId=Attributes.PicId ) ON Pictures.PicId=KeyWords.PicId

--------------------------------
I did this since my test case has been to search for an image I know wasn't appearing in the results recordset. That image only has a KeyWords.Keyword and no Attributes.Data.

Doing this I was able to get a result with a complete record count for that image. I'm now worried though that this may still have a bug that I just currently don't have a test case for.


************* Start My current SQL query *************

SELECT DISTINCT Pictures.PicFilename, Pictures.PicId, Pictures.PicDate

...truncated statement


FROM Pictures LEFT JOIN ( KeyWords LEFT JOIN Attributes ON KeyWords.PicId=Attributes.PicId ) 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

************* END My current SQL query *************

 

[PHV]

You may try this:
FROM (Pictures
LEFT JOIN Attributes ON Pictures.PicId=Attributes.PicId)
LEFT JOIN KeyWords ON Attributes.PicId=KeyWords.PicId

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Bosnoval]

Using my last posted SQL query statistically helps because there are far more records, about a 10:1 ratio, that fall under the case of having a, KeyWords.Keyword and no Attributes.Data, than there are records that have a, Attributes.Data and no KeyWords.Keyword.

However, as expected the test case of a record with a Attributes.Data and no KeyWords.Keyword still fails.

I tried your suggestion of:

FROM (Pictures
LEFT JOIN Attributes ON Pictures.PicId=Attributes.PicId)
LEFT JOIN KeyWords ON Attributes.PicId=KeyWords.PicId

but it only again reverses the problem. Is there a way to modify your suggested JOIN so that the JOIN is performed in two separate steps rather than one nested call? It seems the nesting would cause the Attributes and KeyWords tables to be compared in a way that isn't applicable since it's possible for a valid record to exist in just one, both or neither table.

 

[PHV]

OOps, my bad:
FROM (Pictures
LEFT JOIN Attributes ON Pictures.PicId=Attributes.PicId)
LEFT JOIN KeyWords ON Pictures.PicId=KeyWords.PicId


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Bosnoval]

I thought examples of what I meant by the JOIN comparison may help...

Two examples of a valid record that exist in just one table:

1) A call for a KeyWords.Keyword but "Any" Attributes.Data
2) The reverse, a call for a Attributes.Data but "Any" KeyWords.Keyword

One example of a valid record that would exist in both the Attributes and KeyWords table

1) A specific call for both a KeyWords.Keyword and a Attributes.Data

One example of a call where a valid record wouldn't necessarily exist in either the Attributes or KeyWords table.

1) A call for "Any" KeyWords.Keyword, "Any" Attributes.Data and a specifed date range of 1/1/1916 - Now().

 

[Bosnoval]

Ah wow, thank you! I believe that has got it.


************* Start My current SQL query *************

SELECT DISTINCT Pictures.PicFilename, Pictures.PicId, Pictures.PicDate

...truncated statement


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

************* END My current SQL query *************