[stearmandriver]

Geez, this is turning out to be trickier than I thought. Basically, what I'm trying to do is implement a Coldfusion search form on a basic "products" website. The business handles fruit juice products. So for instance, if the user searches for "apple", they'll get a list of all apple products - apple juice concentrate, apple essence, apple extracts etc. The CF portion is easy enough, I just need help with the SQL query.

Here's the problem: the products are not all contained in one table. They CAN'T be, because different types of products require different types of specifications. So, I've got each product type in its own table. I want a SQL query that will search the PRODUCT_NAME column of all the tables for values containing the user's search term. I understand I'll need some kind of join for this, but the online resources I've looked at all seem to gloss over joins, which is surprising as they seem essential to database use.

Anyhow, here's a simplified example... if anyone can provide an example query for this scenario, I'll be able to figure it out:

1. We have 3 tables - EXTRACTS, ESSENCES, CONCENTRATES. They contain different types of info, but all of them contain a column called PRODUCT_NAME, which is a text data type.

2. I need a query which selects each record FROM ALL 3 TABLES that contains the word, say, "apple" in the PRODUCT_NAME column.

And that's it. Seems like it oughta be simple, but I just can't make it work! Any help would be great....oh, and it's an MS Access database.

Thanks...

[Advertisements]

Hello stearmandriver,

You probably wouldn't want to use a JOIN for this, since I imagine you would want each record returned as an individual record. I imagine that UNION ALL would be more what you need:

SELECT PRODUCT_NAME FROM EXTRACTS
WHERE PRODUCT_NAME = [Product Name]
UNION ALL
SELECT PRODUCT_NAME FROM ESSENCES
WHERE PRODUCT_NAME = [Product Name]
UNION ALL
SELECT PRODUCT_NAME FROM CONCENTRATES
WHERE PRODUCT_NAME = [Product Name]

Since the other fields contain different types of data depending on the table, I'd also assume they have different column names. If you want to pull back more than just the product name, you'd have to alias the column names to be the same format for all tables in the SQL query.

For example:

SELECT PRODUCT_NAME, EXTRACTS_INFORMATION AS INFORMATION FROM EXTRACTS
WHERE PRODUCT_NAME = [Product Name]
UNION ALL
SELECT PRODUCT_NAME ESSENCES_INFORMATION AS INFORMATION FROM ESSENCES
WHERE PRODUCT_NAME = [Product Name]
UNION ALL
SELECT PRODUCT_NAME CONCENTRATES_INFORMATION AS INFORMATION FROM CONCENTRATES
WHERE PRODUCT_NAME = [Product Name]

This way, the query pulls back two columns, PRODUCT_NAME and INFORMATION.

If this doesn't work out for you, there's another way to bring back all the fields, but it's a bit longer. Let me know if this helps.

Ax

[Ax238]

Hello stearmandriver,

You probably wouldn't want to use a JOIN for this, since I imagine you would want each record returned as an individual record. I imagine that UNION ALL would be more what you need:

SELECT PRODUCT_NAME FROM EXTRACTS
WHERE PRODUCT_NAME = [Product Name]
UNION ALL
SELECT PRODUCT_NAME FROM ESSENCES
WHERE PRODUCT_NAME = [Product Name]
UNION ALL
SELECT PRODUCT_NAME FROM CONCENTRATES
WHERE PRODUCT_NAME = [Product Name]

Since the other fields contain different types of data depending on the table, I'd also assume they have different column names. If you want to pull back more than just the product name, you'd have to alias the column names to be the same format for all tables in the SQL query.

For example:

SELECT PRODUCT_NAME, EXTRACTS_INFORMATION AS INFORMATION FROM EXTRACTS
WHERE PRODUCT_NAME = [Product Name]
UNION ALL
SELECT PRODUCT_NAME ESSENCES_INFORMATION AS INFORMATION FROM ESSENCES
WHERE PRODUCT_NAME = [Product Name]
UNION ALL
SELECT PRODUCT_NAME CONCENTRATES_INFORMATION AS INFORMATION FROM CONCENTRATES
WHERE PRODUCT_NAME = [Product Name]

This way, the query pulls back two columns, PRODUCT_NAME and INFORMATION.

If this doesn't work out for you, there's another way to bring back all the fields, but it's a bit longer. Let me know if this helps.

Ax

[stearmandriver]

You, my friend, have skills. The first snippet works PERFECT. All I want for this is the Product Name; that'll be linked to a full spec sheet.

So, problem solved. I never even came across the UNION statement in my research. Clearly I need a better resource!

Anyway, THANKS...

[Ax238]

Not a problem, glad to help. I'm also glad that it worked out for you. Oh, and if you are wanting product names based on any part of the field, you'll probably want to use the LIKE operator and wrap the "product name" value with the "*" (Jet) or "%" operator on both sides.

For example, with a product name search of nut, you'd want to search for *nut* or %nut%, depending on the driver you are using. You would also need to change the WHERE clause from

WHERE PRODUCT_NAME = [Product Name]

to

WHERE PRODUCT_NAME LIKE [Product Name]

Regards,

Ax