SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Create View from multiple databases?

    Hello all, this is my first experience with Views so please be gentle if what I'm asking is dumb. A view may not even be what is needed for this?

    I have multiple databases (for this instance let's say there are 20 of them, in reality there will be a couple hundred) In each of these databases there is a 'users' table. Each of these users tables are structured exactly the same. These databases may, or may not reside on the same server, currently they are all on the same server but in the future it will expand to other servers.

    I need (I think) to create a view that includes all the users tables combined. I want to do this so I can have a 'global' login form for users to use.

    So the view would really only need to include the email address and the db info from which that email address came from.

    My goal is for a visitor to submit the global login form, the script checks the view to see if the email address exists and then checks which db to use to login as then the script queries the correct db and verifies the user (the verification script is already written and functioning).

    If there's a better way to do this I'm open to suggestions/ideas/pointers/advice/guidance. Thanks in advance!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    one obviously better way would be ~not~ to have hunnerts of databases, but just one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yup, this has been considered. I'll look at it again and see why we decided this wasn't a viable solution.

  4. #4
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I think our reasoning behind keeping the db's separate was mainly portability. Each Manufacturer (client) has hundreds of distributors (which may or may not be our clients). What normally happens is that after a mfg signs on with us many of their distributors follow suit. So having the db's separate helps us to quickly and easily roll each distributor into their own full solution.

    Each mfg normally goes on their own dedicated server and we have several other servers that are loaded up with distributor accounts (some full solution, some partial). With each 'client' basically having their own db setup we can easily shift accounts to new servers as traffic or client volume requires.

  5. #5
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe the solution is to create a global users table? Bring all the users into one database?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    maybe
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, that's what we're going to do. Gonna take some re-coding and re-formulation but I think it's for the best long-term. So, for this question the view is answered.

    But... I'm still curious, is it possible to setup a view that includes tables from many different databases?

    Let's say we have db1 db2 and db3

    Each have tbl1 tbl2 and tbl2

    And I want a view (or a query of some sort) that gives me all the records for tbl2 for all databases. Is this possible?

  8. #8
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, that's what we're going to do. Gonna take some re-coding and re-formulation but I think it's for the best long-term. So, for this question the view is answered.

    But... I'm still curious, is it possible to setup a view that includes tables from many different databases?

    Let's say we have db1 db2 and db3

    Each have tbl1 tbl2 and tbl2

    And I want a view (or a query of some sort) that gives me all the records for tbl2 for all databases. Is this possible?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by rustybuddy View Post
    I'm still curious, is it possible to setup a view that includes tables from many different databases?
    it would take me about 10 minutes to test that, because i would have to create some different databases, then create the tables, and so on

    how long will it take you? i mean, you've already got the databases and the tables, so...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Definitely not asking anyone to test anything. But I'm not even sure where to begin to put together a VIEW for multiple databases. Already spent some time on goog, guess I'll keep on searching.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by rustybuddy View Post
    Definitely Already spent some time on goog, guess I'll keep on searching.
    you should have started with da manual ---
    A view can refer to columns of tables or views in other databases by qualifying the table or view name with the proper database name.
    then you will want to have a UNION query because each table will require a separate SELECT -- the view should be created on the UNION query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •