SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2011
    Posts
    173
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to inner join two database?

    Hi,,

    Can anyone have the solution how to inner join the two different database.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,494
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    You cannot inner join two databases.
    You can inner join two tables.

    How? Using inner join

    If you want help with a specific query, you'll have to post more details.

  3. #3
    SitePoint Zealot
    Join Date
    Mar 2011
    Posts
    173
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for the reply..

    I have the first database which i must view all the user and the second database it is where i must save all the user.
    and after saving the value on the first database it must not viewed on.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,494
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    So you have two separate databases, and each database has a user table?
    The two user tables are identical?

    What database is it? MySQL?

  5. #5
    SitePoint Zealot
    Join Date
    Mar 2011
    Posts
    173
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    slightly different the db1 contains more fields than the db2.but the content is the same.

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why is the data not all in one table perhaps with a new column, to flag specific records? ie those which are currently in table two could be flagged as approved or whatever your reasoning is to identifyi them separately.

    bazz

  7. #7
    SitePoint Zealot
    Join Date
    Mar 2011
    Posts
    173
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the first database is the existing one..i must get all the existing data and pass it to the second database..
    the reason that i want to use the inner join is to exclude the one that i save on second database..

    Or if not possible is there a way to connect two database on the same host in one php page.

  8. #8
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am still trying to understand what you are trying to do. is this a move to a new db or are you checking data in the first table and when checked, you want to move it to the second one?

    bazz

  9. #9
    SitePoint Zealot
    Join Date
    Mar 2011
    Posts
    173
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yup..that's the idea...and if they have the same UserName it must be viewed.


    here is my complete script..
    Code PHP:
    <?php
    session_start();
    if(isset($_SESSION['username'])){
     
     
    $id = $_GET['ID'];
    ?>
    	<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    	<meta http-equiv="content-type" content="text/html; charset=utf-8" />
    	<style type="text/css" title="currentStyle">
    		@import "css/demo_page.css";
    		@import "css/demo_table.css";
    	</style>
    	<script type="text/javascript" language="javascript" src="jquery./jquery.js"></script>
    	<script type="text/javascript" language="javascript" src="jquery./jquery.dataTables.js"></script>
    	<script type="text/javascript" charset="utf-8">
    		$(document).ready(function() {
    		$('#example').dataTable();
    		} );
    	</script>
    </head>
    <br/>
    <br/>
    <br/>
    <body id="dt_example">
     
    <p>		
    <table  BORDERCOLOR="#0B0B0B" border='1' bgcolor="#F5F5FA" width="80%" align='center'>
    <tr>
    	<td width="100%" align="left" valign="middle">
    		<img src='img/viewusers.png'>
    	</td>
     
    </tr>
     
     
     
    <tr>
     
    </tr>
    <tr>
     
    	<td width="100%" align="center" >	
     
    		<?php
    		//second database,add the data from the first database
    		include_once("conn.php");
    		if ($id != ""){
     
    			mssql_query("sp_insertuser @uname='$_GET[user]',@lname='$_GET[lname]',@fname='$_GET[fname]',@email='$_GET[email]',@dept='$_GET[dept]',@Cby=$_SESSION[username],@userlvl=$_GET[lvl]");
    		}
    		$uname=mssql_fetch_assoc(mssql_query("select UserName from tblUser"));
    		//my first database,check the existing data
    		include_once("fjscconn.php");
    				$results=mssql_query("Select * from users where user_login!='$uname[UserName]'");
     
    	?>
     
     
     
    		<div id="containeradd">
     
    			<div align='left' id="demo">
     
    				<table BORDERCOLOR="#0B0B0B" cellpadding="0" cellspacing="0" frame="box" class="display" id="example">
     
    					<thead>
    						<tr>
    							<th><a>Username</a></th>
    							<th><a>Full Name</a></th>
    							<th><a>Email</a></th>
    							<th><a>Department</a></th>
    							<th><a>UserLevel</a></th>
    							<th><a>Action</a></th>
    						</tr>
    					</thead>
    					<tbody>
    						<?php while ($row=mssql_fetch_assoc($results)) { ?>
    							<tr class="GradeA">
    								<td class="center"><?php echo $row['user_login'];?></td>
    								<td class="center"><?php echo $row['user_first_name']." ".$row['user_last_name'];?></td>
    								<td class="center"><?php echo $row['user_email'];?></td>
    								<td class="center"><?php echo $row['emp_department']; ?></td>
    								<td class="center"><?php echo $row['usrlevel']; ?></td>
    								<td class="center"><?php echo "<a href='Users.php?ID={$row['user_id']}&user={$row['user_login']}&fname={$row['user_first_name']}&lname={$row['user_last_name']}&email={$row['user_email']}&dept={$row['emp_department']}&lvl={$row['usrlevel']}' onClick=\"javascript: var x=window.confirm('Add User ".$row['user_login']."?');if (!x) return(false);\" title='Add User'>Add User</a>" ?></td>
     
    							</tr>
    						<?php }	?>
    					</tbody>
    					<tfoot>
    						<tr>
    							<th>Username</th>
    							<th>Full Name</th>
    							<th>Email</th>
    							<th>Department</th>
    							<th>UserLevel</th>
    							<th>Action</th>
    						</tr>
    					</tfoot>
     
     
    				</table>
     
    			</div>
     
    		</div>		
     
    	</td>
    </tr>
    </table>
    </p>
    </body>
    </html>
    <?php
    }else{ //not logged in
        header('location: login.php');
    }
    ?>

  10. #10
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if the username is the same it must be viewed.
    Does that mean, it should show up for you to check it, if they are the same people - like a manual verification?

    please show your create table statement for table 1. I need to see if the username is unique because, if it is, then how could you have accidental (or any) duplicates?

    and if there would be no dupes of people then when inserting to table 2, I would use ON DUPLICATE KEY UPDATE.

  11. #11
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    would something like this not work?

    Code MySQL:
    insert ignore into table 2
    ( col1, col2)
    select
    col1
    , col2
    from table 1

    make sure you back up the table 2 before doing that though.

  12. #12
    SitePoint Enthusiast
    Join Date
    Feb 2011
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you can inner join by using the plus operator on right side of equal to operator

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jargonbust View Post
    you can inner join by using the plus operator on right side of equal to operator
    two comments:

    1. that would make it an outer join, not an inner join

    2. that syntax is horrible as well as deprecated -- use OUTER JOIN syntax if you need to have an outer join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    Non-Member
    Join Date
    Aug 2010
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this:

    SELECT a.userID, b.usersFirstName, b.usersLastName FROM databaseA.dbo.TableA a
    inner join database B.dbo.TableB b ON a.userID=b.userID

  15. #15
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have two different MSSQL databases on two different servers that i need to join.
    Table1 on one server
    Table 2 on the other

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by webmaster128 View Post
    I have two different MSSQL databases on two different servers that i need to join.
    see post #14 in this thread

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

  17. #17
    SitePoint Zealot
    Join Date
    Mar 2011
    Posts
    173
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i was just wandering what is DBO?

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    "dbo" stands for "data base owner" and it is the owner of the database
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Zealot LinuxFreelancer's Avatar
    Join Date
    Jun 2011
    Location
    Boston, Ma, Usa
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why in God's name are you using 2 databases? The performance must be horrific, not to mention syncing must be a pain in the rear.

    Quote Originally Posted by Timmytell View Post
    Try this:

    SELECT a.userID, b.usersFirstName, b.usersLastName FROM databaseA.dbo.TableA a
    inner join database B.dbo.TableB b ON a.userID=b.userID
    Might I recommend
    Amazon.com: PHP and MySQL for Dummies with CDROM (0785555108622): Janet Valade: Books

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by LinuxFreelancer View Post
    Might I recommend ...
    you might

    but we would like you a whole lot more if you recommended the sitepoint books on the same subject instead

    also it's a good idea to watch your language, especially the religious references

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

  21. #21
    SitePoint Zealot LinuxFreelancer's Avatar
    Join Date
    Jun 2011
    Location
    Boston, Ma, Usa
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry new here, didn't notice the site sold books.


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
  •