We help IT Professionals succeed at work.

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:

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 NULL

Open in new window


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.
Comment
Watch Question

Commented:
LouisvilleGeek:

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].[dbo].[EquipmentClock] ([equipmentId],[Clk.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
      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

Commented:
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.

Author

Commented:
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?

Commented:
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.

Author

Commented:
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?

Commented:
You can simply use management studio, go to particulare database->table->
right click-> create table.
You will get the schema there.

Author

Commented:
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?
...also to clarify from earlier, here's the tables for clock and the reference table.

The issue I'm having is that the Job field is in another table, in another DB.  I have to take that job number, compare it to the value for crewId and somehow return the clockId for that crew in my original insert statement.

b.Job == clk.crewId would should return clk.clockId for my statement.
tables.PNG

Author

Commented:
abandonded Q

Explore More ContentExplore courses, solutions, and other research materials related to this topic.