Multiple Joins
I need help structuring a statement for the following scenario.
I am dealing with two databases that are linked. Ultimately, I creating an insert statement that will import two fields (equipmentId and clockId) into a reference table named EquipmentClock.
In order to obtain equipmentId, I have to reference a table named bEMEM which lives in another database. The bEMEM table contains a list of equipment and the jobs they are assigned to.
Originally, I was planning on setting the values in clockId to match job numbers, so my initial SQL statement was as follows:
However, as I came to find out, its not that easy. It turns out that there's another ID that I must use for clockId, and it differs from what would normally be placed in the job field.
This has resulted in dragging a third table into the situation named Clock. In order to insert the correct clockId into the EquipmentClock table, I now must reference the Job field from the bEMEM table against a field named crewName in the Clock table, which will then return the correct clockId table for the insert.
I'm guessing this will result in some form of nested join, but I'm really having a hard time conceptualizing the statement. Any help is greatly appreciated.
I am dealing with two databases that are linked. Ultimately, I creating an insert statement that will import two fields (equipmentId and clockId) into a reference table named EquipmentClock.
In order to obtain equipmentId, I have to reference a table named bEMEM which lives in another database. The bEMEM table contains a list of equipment and the jobs they are assigned to.
Originally, I was planning on setting the values in clockId to match job numbers, so my initial SQL statement was as follows:
INSERT INTO [CHSQL01\ABOUTTIME].[abtt].[dbo].[EquipmentClock] ([equipmentId],[clockId])
SELECT equipmentId, (LEFT(b.Job,5)) AS clockId
FROM [CHSQL01\ABOUTTIME].[abtt].[dbo].[Equipment] a
INNER JOIN VPTest.dbo.bEMEM b
ON a.[equipmentCode] COLLATE DATABASE_DEFAULT = b.Equipment COLLATE DATABASE_DEFAULT
WHERE b.Job IS NOT NULLHowever, as I came to find out, its not that easy. It turns out that there's another ID that I must use for clockId, and it differs from what would normally be placed in the job field.
This has resulted in dragging a third table into the situation named Clock. In order to insert the correct clockId into the EquipmentClock table, I now must reference the Job field from the bEMEM table against a field named crewName in the Clock table, which will then return the correct clockId table for the insert.
I'm guessing this will result in some form of nested join, but I'm really having a hard time conceptualizing the statement. Any help is greatly appreciated.
how big are the tables?
Can you post the size of the tables with sp_spaceused 'tablename'. I am asking this because it will help checking whether its better to insert data from remote server to the table or to select data from remote server and insert it locally. Just from performance point of view.
Can you post the size of the tables with sp_spaceused 'tablename'. I am asking this because it will help checking whether its better to insert data from remote server to the table or to select data from remote server and insert it locally. Just from performance point of view.
ASKER
AielloJ, thanks for the help. This has gotten me further, however I've run into another issue. As of right now, the second join statement isn't returning any results.
Just to make sure we're on the same page, I need the clock table to return the clockId, which is an integer. What I'm stumped about is that b.Job = clk.crewId and that needs to return the clk.clockId. Would the inner join you described accomplish this?
Just to make sure we're on the same page, I need the clock table to return the clockId, which is an integer. What I'm stumped about is that b.Job = clk.crewId and that needs to return the clk.clockId. Would the inner join you described accomplish this?
Change the condition to what you think is right..
ON b.Job = Clk.crewName
So if you know it should be crewid modify it
ON b.job = Clk.crewID
Again, if you post the full schema, it will be much easier for experts to help you with the issue.
ON b.Job = Clk.crewName
So if you know it should be crewid modify it
ON b.job = Clk.crewID
Again, if you post the full schema, it will be much easier for experts to help you with the issue.
ASKER
What would be the best method for doing this? It's an MSSQL DB. Any chance of having Management Studio export it, or do I need to fire up Visio?
You can simply use management studio, go to particulare database->table->
right click-> create table.
You will get the schema there.
right click-> create table.
You will get the schema there.
ASKER
Doesn't this just create a new table? What am I missing? The only thing I'm seeing under schema when creating a table is dbo. Are you wanting an ERD of the DB I'm working with?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
abandonded Q
Not tough at all, and no nested joins required if I understand your requirements correctly. You simply keep stringing INNER JOIN definitions on all the tables that have data you need, and the columns they join on. Second, replace the original clockID in the first line of your INSERT statement with the one from Clock table. You may have to adjust the syntax a bit, but the statement below should work for you.
INSERT INTO [CHSQL01\ABOUTTIME].[abtt]
SELECT equipmentId, (LEFT(b.Job,5)) AS clockId
FROM [CHSQL01\ABOUTTIME].[abtt]
INNER JOIN VPTest.dbo.bEMEM b
ON a.[equipmentCode] COLLATE DATABASE_DEFAULT = b.Equipment COLLATE DATABASE_DEFAULT
INNER JOIN Clock Clk
ON b.Job = Clk.crewName
WHERE b.Job IS NOT NULL
A helpful debugging hint is to copy and paste the SELECT part of the statement into your debugging tool and see if what is returned is what you want.
Regards,
AielloJ