Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sbt1
Yak Posting Veteran
89 Posts |
 Posted - 2006-09-11 : 20:33:13
|
| I am writing a search engine in a database at work. I'd like to be able to run queries against multiple tables and somehow merge the results into a single recordset to display and let the user select from.For example, if the user enters a search string I'd like to be able to search for that string in Table1, Table2, Table3, etc. Of course, each will have to have its own SELECT statement since the fields of each table are completely different. Assuming I write the SELECTs to return the same # of fields, how would I write such a query? |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-09-11 : 21:54:11
|
| SELECT 'Search1' as ID, field1 as f1, field2 as f2FROM table1WHERE .....UNIONSELECT 'Search2' as ID, field4 as f1, field5 as f2FROM table2WHERE ....Basically use the same field aliases in each and create a UNION. |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-09-11 : 23:50:53
|
and make sure the field is of the same datatype. You can't have field f1 in query 1 returning varchar and f1 in query 2 returning integer.
KH |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-09-12 : 00:44:14
|
quote:
Originally posted by byrmol
>> You can't have field f1 in query 1 returning varchar and f1 in query 2 returning integer.Yes you can...http://weblogs.sqlteam.com/davidm/posts/5016.aspxDavidMProduction is just another testing cycle
oh  . . . but sounds messy. Wonder how would the front end react to these type of data.
KH |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-09-12 : 00:56:52
|
| Shouldn't matter.... The returned cursor/recordset will have a datatype that, in this case, would be the lowest common denominator (i.e. the only datatype that would take all the values within). |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
|
|
|
|
|
|
|
Merging results of multiple queries into one?