Recently i had the task of querying multiple tables in multiple databases in Microsoft SQL Server. I thought i’d share it online for anyone else who may be looking at or struggling doing the same thing.
Once you see the code it makes sense and is fairly straight forward but i will comment the code so you can see what does what etc.
Lets say you have a database containing sales (which we’ll call shop_sales) and another containing customers (which we’ll call shop_customers).
The customers database has a table in it called custdetails which has the columns id, firstname and lastname.
The sales database has a table called solditems which has a column of custid, item, price, transactionid and a table called payments which has a field of custid, transactionid paid and total.
Obviously in real life they’d probably have alot more columns in them! (and most likely be in the same database)
Our id from customers table and the custid from the payments and solditems table are all the same so we can use these to create joins on our databases.
We’ll be looking at running a query on the database to find people who have bought products but haven’t paid for them yet (not sure when this would happen but ohwell!) if a customer has paid the colum paid will have a 1 in it else it’ll have a 0.
So lets start with our query.
SELECT db1.firstname AS FirstName, db1.lastname AS LastName, db2.item AS ItemSold, db2.price AS Price from shop_customers..custdetails AS db1 join shop_sales..solditems AS db2 on db1.id = db2.custid join shop_sales..payments as db3 on db2.custid = db3.custid where db3.paid = 0
So now we can look at more detail at what each of the lines of code do:
Note:- Im using AS to make the query display a bit nicer on output, else it would just show the column name as the results
(1) Select Customer Firstname from db1 which is our shop_customers database and we select the customerdetails table. – specifying what db1 is happens at line 5
(2) Select Customer Lastname from db1 which is our shop_customers database and we select the customerdetails table. – specifying what db1 is happens at line 5
(3) Select Description of Item Sold from our second database db2 which is shop_sales and selecting the solditems table. – specifying what db2 is happens at line 6
(4) Select Price of Item Sold from database 2 which is shop_sales and also selecting the solditems table again. – specifying what db2 is happens at line 6
(5) Setting db1 as shop_customers and the table custdetails this part sets what database and table db1 is.
(6) Setting db2 as shop_sales and the table solditems this line sets what database and table db2 is
(7) This line connects db1 and db2 by customer id number allowing the query to have a regular number accross databases
(8) Setting db3 as shop_sales and the table payments so we can check whether things have been paid or not this isnt linking a new database as such but this is the best way i’ve found to link a third table into the equation and it keeps everything uniform and the same meaning it’s easier to work with.
(9) Then we perform the same action as step 7 by connecting db2 and db3 (both the same database but different tables in that database) once again using customer id to link the two tables.
(10) Lastly, we check to make sure that the only results we get are where paid = 0 meaning the customer has not paid.
I hope you’ve found this tutorial usefull. If you have any questions or querys feel free to get in touch via the comments below.