November 20, 2014 at 5:35 am
I am new to sql server so I appreciate all help i can get in this matter.
what I want in a successful report by linking three databases:
DB1.id, DB1.data2, DB1.data3, DB2.data2, DB2.data3, [blankfield if null from DB3]
DB1.id, DB1.data2, DB1.data3, DB2.data2, DB2.data3, DB3.custodian
Me trying to explain my problem:
I deal with a check-in-check-out system that utilizes sql server, and my system uses a multitude of databases to pull its data from or alter it as used. I am trying to create a report that will pull data from 2 databases ("DB1" & "DB2") all the time, and from a third database ("DB3") if the item is checked out but if it is not checked out then that part of the report would appear blank. I have DB1 and DB2 talking correctly, but I cannot figure out how to get the report to work like I want when I link DB3.
Right now I have DB1 and DB2 linked thru a common category of "id" (the ID of the item being checked out), so:
DB1.id = DB2.id
which allows me to show the report with the following 5 following categories where the DB2 info is correctly displayed along with the DB1 info for the particular ID:
DB1.id, DB1.data2, DB1.data3, DB2.data2, DB2.data3, [blankfield/null from DB3]
When the particular ID is NOT checked out from my system, the above info is what I want displayed.
However, if the item IS checked out, the change is recorded in DB3, meaning I believe that DB3 would need to be linked to DB1 or DB2. All three DB's have a common "**.id" category so i tried linking that:
db1.id = db2.id and db1.id = db3.id
but when I do this my report only shows report data for IDs that are currently checked out, where I desire the report data for items regardless of their checkout status.
I hope my explanataion makes sense. thank you for your help with this challenge!
November 20, 2014 at 1:12 pm
scott.foss (11/20/2014)
I am new to sql server so I appreciate all help i can get in this matter.what I want in a successful report by linking three databases:
DB1.id, DB1.data2, DB1.data3, DB2.data2, DB2.data3, [blankfield if null from DB3]
DB1.id, DB1.data2, DB1.data3, DB2.data2, DB2.data3, DB3.custodian
Me trying to explain my problem:
I deal with a check-in-check-out system that utilizes sql server, and my system uses a multitude of databases to pull its data from or alter it as used. I am trying to create a report that will pull data from 2 databases ("DB1" & "DB2") all the time, and from a third database ("DB3") if the item is checked out but if it is not checked out then that part of the report would appear blank. I have DB1 and DB2 talking correctly, but I cannot figure out how to get the report to work like I want when I link DB3.
Right now I have DB1 and DB2 linked thru a common category of "id" (the ID of the item being checked out), so:
DB1.id = DB2.id
which allows me to show the report with the following 5 following categories where the DB2 info is correctly displayed along with the DB1 info for the particular ID:
DB1.id, DB1.data2, DB1.data3, DB2.data2, DB2.data3, [blankfield/null from DB3]
When the particular ID is NOT checked out from my system, the above info is what I want displayed.
However, if the item IS checked out, the change is recorded in DB3, meaning I believe that DB3 would need to be linked to DB1 or DB2. All three DB's have a common "**.id" category so i tried linking that:
db1.id = db2.id and db1.id = db3.id
but when I do this my report only shows report data for IDs that are currently checked out, where I desire the report data for items regardless of their checkout status.
I hope my explanataion makes sense. thank you for your help with this challenge!
First a SQL instance holds databases, a database holds tables, a table holds data.
You can use a SELECT statement with JOINs to get the information you request.
I assume DB1, DB2, and DB3 are tables as you are indicating they have a column called "id".
SELECT DB1.id, DB1.data2, DB1.data3, DB2.data2, DB2.data3, DB3.custodian
FROM DB1
LEFT JOIN DB2 ON DB1.ID = DB2.ID
LEFT JOIN DB3 ON DB1.ID = DB3.ID;
This will give you records for all IDs in DB1, if there is matching data in DB2 or DB3 it will be displayed.
I have just hit the highlights here so good luck.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply