Inspiring

Question

Merging results from 3 different queries

Forum|Forum|16 years ago
November 18, 2009
3 replies
830 views

Hello people, I was wondering if someone could help me with this.

I have a search by keyword feature that will bring results from 3 different tables in the following way:

We have the following tables:

Prop (prop description, image location and propID basically)
PropCat (catID, catName)
PropSub (subID, subName and catID)
propHist (each prop can be in many categories/subcategories. table structure: histID, propID, catID, subID)

When someone types a keyword to search for props, this is what happens (see attached jpg for a screen capture):

Results by prop name:

I have 1 query that searches the keyword in the prop name.

"Props found where keyword found in prop name."

(all the props here)

Results by Category name:

I have 1 query that searches the keyword in the category name and returns the IDs

A loop thru the propHist (another query) searching all the propIDs found in the catID from the above query.

Once the query retrieves the propID, another query to get me the propName, location and image associated to it.

Display results.

I close the loop.

Results by SubCategory name:

I have 1 query that searches the keyword in the subcategory name and returns the IDs.

A loop thru the propHist (another query) searching all the propIDs found in the subID from the above query.

Once the query retrieves the propID, another query to get me the propName, location and image associated to it.

Display results.

I close the loop.

Now, I know it's not the correct way but I'm getting the results I need when I do the search. One problem is that results may repeat since the keyword may be found in the prop name and the category name as well.

I would love to bring a consolidate result set.

So instead of separating by prop name, category name and subcategory name, just a simple result with all the results retrieved above, but avoiding repetitions.

Too confusing?

Any help will be welcome.

-------------------------------
Dani Szwarc
Thiez Multimedia
dani@thiez.com

This topic has been closed for replies.

Dani SzwarcAuthor

Inspiring

Guys, thanks for your seggestions, I made some changes on the table structure and will try with the simple query as Dan suggested.

I'll post the results.

Thanks again for taking the time to read, think and answer my question.

-------------------------------
Dani Szwarc
Thiez Multimedia
dani@thiez.com

Dani SzwarcAuthor

Inspiring

Yup guys, it worked.

I basically modified the propHist table by adding also the names for prop, category and subcategory, not only the ids.

Then I created this query:

<cfquery name="getProp" datasource="#ds#">
SELECT DISTINCT propID FROM propHist
where propName like "%#form.image#%"
or propCat like "%#form.image#%"
or propSub like "%#form.image#%"
</cfquery>

Then a loop thru the prop table to get names, locations and images and it worked beatifully.

Thanks again guys.

-------------------------------
Dani Szwarc
Thiez Multimedia
dani@thiez.com

D

Dan_Bracuk

Inspiring

Assuming it's all the same database, there are two ways to accomplish this with a single query. You could do a union query, or, a normal query with a where clause like this

where field1 like '%somestring%'

or field2 like '%somestring%'

etc

chrisbowyer

Known Participant

Maybe the following MS Access image search query will be of help to you...

<cfsilent>
<cfquery name="getImageInfo" datasource="#request.dsn#">
SELECT
    IMAGE.IMAGE_ID,
    IMAGE.EXT,
    IMAGE.TITLE,
    ALBUM.ALBUM,
    IMAGE.DATE_TIME,
    IMAGE.DATE_DIFFERENCE,
    IMAGE.LOCATION,
    IMAGE.PLACE,
    IMAGE.PEOPLE,
    IMAGE.OTHER,
    IMAGE.COMMENT,
    OWNER.OWNER,
    IMAGE.ORIGINAL_HEIGHT,
    IMAGE.ORIGINAL_WIDTH
FROM
    (
    IMAGE
INNER JOIN
    ALBUM
ON
    IMAGE.ALBUM_ID = ALBUM.ALBUM_ID
    )
INNER JOIN
    OWNER
ON
    ALBUM.OWNER_ID = OWNER.OWNER_ID
WHERE
    IMAGE.ALBUM_ID = <cfqueryparam value="#variables.albumId#" cfsqltype="cf_sql_integer">
    <cfif variables.search NEQ "">
        AND
        IMAGE.PEOPLE LIKE <cfqueryparam value="%#variables.search#%" cfsqltype="cf_sql_varchar">
        OR
        IMAGE.ALBUM_ID = <cfqueryparam value="#variables.albumId#" cfsqltype="cf_sql_integer">
        AND
        IMAGE.LOCATION LIKE <cfqueryparam value="%#variables.search#%" cfsqltype="cf_sql_varchar">
        OR
        IMAGE.ALBUM_ID = <cfqueryparam value="#variables.albumId#" cfsqltype="cf_sql_integer">
        AND
        IMAGE.PLACE LIKE <cfqueryparam value="%#variables.search#%" cfsqltype="cf_sql_varchar">
        OR
        IMAGE.ALBUM_ID = <cfqueryparam value="#variables.albumId#" cfsqltype="cf_sql_integer">
        AND
        IMAGE.TITLE LIKE <cfqueryparam value="%#variables.search#%" cfsqltype="cf_sql_varchar">
        OR
        IMAGE.ALBUM_ID = <cfqueryparam value="#variables.albumId#" cfsqltype="cf_sql_integer">
        AND
        IMAGE.OTHER LIKE <cfqueryparam value="%#variables.search#%" cfsqltype="cf_sql_varchar">
        OR
        IMAGE.ALBUM_ID = <cfqueryparam value="#variables.albumId#" cfsqltype="cf_sql_integer">
        AND
        IMAGE.KEYWORD LIKE <cfqueryparam value="%#variables.search#%" cfsqltype="cf_sql_varchar">
        OR
        IMAGE.ALBUM_ID = <cfqueryparam value="#variables.albumId#" cfsqltype="cf_sql_integer">
        AND
        IMAGE.COMMENT LIKE <cfqueryparam value="%#variables.search#%" cfsqltype="cf_sql_varchar">
    </cfif>
</cfquery>
</cfsilent>

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded