[thelist] Multiple Database Union?

[Jeff Howden]

russ,

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> From: Russ
> 
> I've got a page where we'd like to do a union from
> multiple databases on the same server, using ColdFusion
> MX and MySQL.
> 
> Something like this:
> 
> Select  Title, Story, Date, Time and commentCount
> from database1.tblName
> Union
> Select  Title, Story, Date, Time and commentCount
> from database2.tblName
> union....
> 
> The issue at hand is that I'm unsure how to query across
> databases--more specifically how to specify the database
> in the FROM clause.
><><><><><><><><><><><><><><><><><><><><><><><><><><><><><

perhaps query of queries could assist here.

<cfquery name="getstories1" datasource="dsn1">
  SELECT title
       , story
       , date
       , time
    FROM tblName
</cfquery>

<cfquery name="getstories2" datasource="dsn2">
  SELECT title
       , story
       , date
       , time
    FROM tblName
</cfquery>

<cfquery name="getstories" dbtype="query">
  SELECT *
    FROM getstories1
   UNION
  SELECT *
    FROM getstories2
</cfquery>

.jeff

------------------------------------------------------
Jeff Howden - Web Application Specialist
Resume - http://jeffhowden.com/about/resume/
Code Library - http://evolt.jeffhowden.com/jeff/code/