Complex SQL query... can it be done?

[Spleef]

I'm not sure if this is possible? I want to do a site-wide search, which means querying at least three different tables, and outputing in one seamless Search Results page. I am using Coldfusion/Access.
The tables/fields are as follows:
Pages:
- Name
- Description

Products:
- ProductName
- SDescription

News:
- Heading
- Description

Any help on this would be greatly appreciated :D ... (My head leans more towards designer than developer)

[dthurman1432]

google this "query from multiple tables"
It shows a lot of solutions

[Spleef]

Fair enough. In fact I have been Google-ing this query for weeks already, yet I'm still no closer to a solution. I can create separate queries for each table, and then query those, however ColdFusion won't let me query more than two queries at one time. My big conundrum is how to output these differently named tables in one seamless results page. I would like to limit the output to say 10 records per page and have a next/previous facility, so it ultimately has to come from one query. Forgive me if this is a stupid question, I am truly stifled!

[sridhar_423]

If I'm not wrong, the User enters the Product Name and you try him to show the the various Pages with news of Each Page. Which means...

Product Name(Primary Key) <-- User enters this one
From this, you fetch Description?? after that??... I cant figure out the relationship you've build between the tables. Might be, I have analysed it wrongly. Can you be a bit clearer?
I dont know Cold Fusion.. Only Oracle(SQL) :) ..

[Spleef]

My end goal is the following: I want to create a site-wide seach facility that will index any information (pages, news, or products) that matches the users search criteria. My search will query multiple fields in multiple tables, and output in a single search results format. My current query looks something like this:
<CfQuery name="Search" datasource="#Request.mainDSN#">
SELECT Name as pgn, pages.description as pgd, productname, sdescription, heading, news.description
FROM pages, products, news
WHERE (pages.content like '%#attributes.criteria#%') OR (pages.name like '%#attributes.criteria#%') OR (pages.titleonPage like '%#attributes.criteria#%') OR (pages.description like '%#attributes.criteria#%') OR (products.productName like '%#attributes.criteria#%') OR (products.SDescription like '%#attributes.criteria#%') OR (products.application like '%#attributes.criteria#%') OR (products.keywords like '%#attributes.criteria#%') OR (products.Ldesc like '%#attributes.criteria#%') OR (news.heading like '%#attributes.criteria#%') OR (news.body like '%#attributes.criteria#%') OR (news.description like '%#attributes.criteria#%')
</CfQuery>

[sridhar_423]

this query doesn't have any meaning. Though there is a match in any one record in any one of the tables, tat record will be fetched for sure, but on the other hand, since you're joining all the three tables without any condition, all the rows in the remaining two tables will also be fetched. So, here u hav to use UNION.

un tested
Select Name as PG_NAME,DESCRIPTION as PG_DESCRIPTION, '' as PROD_NAME,'' as PROD_DESCRIPTION, '' as NEWS_HEAD,'' as NEWS_DESCRIPTION from PAGES
where
(content like '%#attributes.criteria#%') OR
(name like '%#attributes.criteria#%') OR
(titleonPage like '%#attributes.criteria#%') OR
(description like '%#attributes.criteria#%')
UNION
Select '','', ProductName,DESCRIPTION,'','' from PRODUCTS
where
(productName like '%#attributes.criteria#%') OR
(SDescription like '%#attributes.criteria#%') OR
(application like '%#attributes.criteria#%') OR
(keywords like '%#attributes.criteria#%') OR
(Ldesc like '%#attributes.criteria#%')
UNION
Select '','', '','',HEADING,NEWS from PRODUCTS
where
(news.heading like '%#attributes.criteria#%') OR
(news.body like '%#attributes.criteria#%') OR
(news.description like '%#attributes.criteria#%')

i'm not sure of your column names. This is the query you're looking for If I have understood you correctly. :)
But Still I'm confused with your column ;)

[Spleef]

You've been very helpful Sridhar. I think we're on the right track.

[sridhar_423]

Be sure that the number of columns you're selecting in each of the Select query are same as "UNION" is used.

[Spleef]

Sridhar you are the man! That worked perfectly, thanks alot! :D