CodeCharge Studio
search Register Login  

Visual Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> Archive -> GotoCode Archive

 Search tables, not just one

Print topic Send  topic

Author Message
Mark
Posted: 11/04/2003, 3:19 PM

Is it possible to search more than one table in a database? I have 6 and I need to search them all but only one field in the table.

Thanks
ryan
Posted: 11/04/2003, 3:25 PM

of course it is possible

either you use INNER JOINS to get the field

or SUBQUERIES to only get the values you want :)
John Kallies
Posted: 11/17/2003, 1:47 PM

It sounds like you want a union query. CC won't build it for you, but most DBMS's support them.

The following query...

SELECT
'PERSON' AS TABLE_NAM,
PERSON_NUM as PARTY_NUM,
LAST_NAM + ', ' + FIRST_NAM AS PARTY_NAM
FROM PERSON
UNION
SELECT
'COMPANY' AS TABLE_NAM,
COMPANY_NUM,
COMPANY_NAM
FROM COMPANY
ORDER BY 1

...Will return a record set with 3 columns, TABLE_NAM, PARTY_NUM, and PARTY_NAM, containing the contents of both tables mixed together, and sorted by the name column.

Selecting a string literal allows you to know which table the data came from. Union querys can be expensive, depending on how they are done. UNION ALL is cheaper, if your DBMS supports it, but isn't as rigid in the expected results.

Be advised -- the need for union queries often indicate the database design wasn't mature to start with. As I haven't seen the database you are coding against, there are many valid reasons to do union queries (give me all the People and Companies in the same report, for example).

Maybe more than you wanted? Hope it helped somehow...

Regards,
John

   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Internet Database

Visually create Web enabled database applications in minutes.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.