Re: SQL Question - Merging files or result sets?

[Adam Glauser <adamglauser@xxxxxxxxxxxx>]

Bradley V. Stone wrote:
> This isn't a join, it's two files with identical layouts, but different
> field names and different category of data.  Otherwise the files are
> identical.
>
> When a user does a search, they can search file A, file B, or both.  So I'd
> like to try to write this dynamic enough so all I do is check the search
> flags they set and build my SQL appropriatly.  Especially when they search
> both so it will "mesh" the data from the two files in the result set.

I think that UNION will do the trick.  Perhaps you could do something like:

selectA = 'SELECT <the fields> FROM fileA WHERE <criteria>'
selectB = 'SELECT <the fields> FROM fileB WHERE <criteria>'
order   = ' ORDER BY <ordering fields>'

if (search = both A and B)
  query = selectA + ' UNION ' + selectB + order

elseif (search = A only)
  query = selectA + order

else
  query = selectB + order

Would something like that do what you're after?