From: Martin Gainty Date: November 21 2008 10:18pm Subject: RE: SELECT through many databases List-Archive: http://lists.mysql.com/mysql/215336 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="_36375663-ae17-4009-9569-7778fb7898a2_" --_36375663-ae17-4009-9569-7778fb7898a2_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable as UNIONS may cause FTS you might want to look at using JOIN USING(ColumnWhichIsCommonForEveryTable) (then place unique Indexes on each of the Columns for all Tables) Martin=20 ______________________________________________=20 Disclaimer and confidentiality note=20 Everything in this e-mail and any attachments relates to the official busin= ess of Sender. This transmission is of a confidential nature and Sender doe= s not endorse distribution to any party other than intended recipient. Send= er does not necessarily endorse content contained within this transmission.= =20 > Date: Fri=2C 21 Nov 2008 14:21:39 -0500 > From: brenttech@stripped > To: andrematos@stripped > Subject: Re: SELECT through many databases > CC: mysql@stripped >=20 > A MERGE table is just a virtual table that is made up of other tables. > You treat it no differently than any other table=2C pretend it's a real > table. >=20 > You could even create multiple MERGE tables from different tables. A > good example is Q1=2C Q2=2C Q3=2C Q4=2C LatestQ=2C for quarterly informat= ion. > You just roll the underlying tables from one to the other as quarters > close=2C you never have to change your scripts or move data around. > Underlying tables can be members of more than one MERGE table. >=20 > You can even INSERT into them=2C just specify which underlying table the > new data is supposed to go into. >=20 > On Fri=2C Nov 21=2C 2008 at 2:12 PM=2C Andre Matos wrote: > > Sounds interesting=2C but does the MERGER support complex SELECT statem= ents > > and LEFT JOIN? > > > > Andre > > > > > > On 21-Nov-08=2C at 1:45 PM=2C Brent Baisley wrote: > > > >> On Fri=2C Nov 21=2C 2008 at 12:44 PM=2C Andre Matos > >> wrote: > >>> > >>> Hello=2C > >>> > >>> Let's suppose I have 5 database: db1=2C db2=2C db3=2C db4=2C and db5.= They all > >>> have > >>> the same structure but different data. > >>> > >>> I would like perform this select > >>> > >>> SELECT TaskDoneOn=2C TaskDoneBy > >>> FROM {database} > >>> WHERE TaskDoneOn IS NOT NULL > >>> > >>> and collect the data from all 5 database. However=2C I would like to = avoid > >>> doing something like this: > >>> > >>> SELECT TaskDoneOn=2C TaskDoneBy > >>> FROM db1 > >>> WHERE TaskDoneOn IS NOT NULL > >>> UNION > >>> SELECT TaskDoneOn=2C TaskDoneBy > >>> FROM db2 > >>> WHERE TaskDoneOn IS NOT NULL > >>> UNION > >>> SELECT TaskDoneOn=2C TaskDoneBy > >>> FROM db3 > >>> WHERE TaskDoneOn IS NOT NULL > >>> UNION > >>> SELECT TaskDoneOn=2C TaskDoneBy > >>> FROM db4 > >>> WHERE TaskDoneOn IS NOT NULL > >>> UNION > >>> SELECT TaskDoneOn=2C TaskDoneBy > >>> FROM db5 > >>> WHERE TaskDoneOn IS NOT NULL > >>> > >>> > >>> Today I have 5=2C but tomorrow I can have 50 and I don't want to forg= et any > >>> database. > >>> > >>> Thanks for any help. > >>> > >>> Andre > >> > >> Create a MERGE table that is all those tables combined. Then you just > >> need to do 1 select as if it was one table. Just be sure to update the > >> MERGE table description when ever you add a table. > >> > >> Brent Baisley > > > > -- > > Dr. Andr=E9 Matos > > andrematos@stripped > > > > > > > > >=20 > --=20 > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@stripped= om >=20 _________________________________________________________________ Color coding for safety: Windows Live Hotmail alerts you to suspicious emai= l. http://windowslive.com/Explore/Hotmail?ocid=3DTXT_TAGLM_WL_hotmail_acq_safe= ty_112008 = --_36375663-ae17-4009-9569-7778fb7898a2_--