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
|
|
|
|
|
|
