I'm not terribly familiar with MySQL. Most of my experience is with Sybase and MS SQL Server (which are very closely related). In these, you simply prefix the table name with the database name and owner to access a table in a different database. (Assuming, of course, that the login has permissions on both databases.)

Suppose for example that you have two databases, common and cust1. The common table has table states which contains the full name of each state (full_name), keyed by its abbreviation (ST). The cust1 database has table customers which has the customer number (Cust_ID) and state (ST). If you're logged into cust1 and want to count all the customers by state and list the state name, you'd do something like this:

select common.dbo.states.full_name, count(customers.*)
from customers
join common.dbo.states on customers.ST = common.dbo.states.ST
group by common.dbo.states.full_name
order by common.dbo.states.full_name

[download]

So it's a bit verbose. But there's another trick you can use in these two databases: You can hide the table in the remote database behind a view so it looks just like a table in the current database. Something like this:

create view states
as select ST, full_name from common.dbo.states

[download]
Now you can perform the previous operation more naturally:

select states.full_name, count(customers.*)
from customers
join states on customers.ST = states.ST
group by states.full_name
order by states.full_name

[download]

As I said earlier, I'm not very familiar with MySQL (not having used it since about v3.1), but I'd imagine that it offers similar capabilities. Hopefully a knowledgeable MySQL or PostgreSQL user will chime in with similar operations, or improved suggestions.

--roboticus

In reply to Re^3: OT: Data Warehousing Strategies by roboticus
in thread OT: Data Warehousing Strategies by Booger

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.