Join not working

[letoii]

This SQL statement is not grabbing s.badge_no and joining it.

I want to match the "s.lname" field using the "like" operator to the "n.name" field and join n.badge_no to the corresponding row in name_ID.

DELETE FROM stemp
         DECLARE 
	@Id integer, 
	@name varchar(50),
	@password varchar(50), 
	@dept varchar(50), 
	@division char(10), 	
	@active bit, 
                @badge_no char(10)
        DECLARE S_CURSOR CURSOR FOR
                SELECT n.Id, n.name, n.password, n.dept, n.division, n.active, s.badge_no
                FROM nameid n 
            	JOIN  server.dbo.employees s ON n.name LIKE '%' + s.lname + '%' 
        OPEN S_CURSOR FETCH NEXT FROM  S_CURSOR
	INTO @Id, @name, @password, @dept, @division, @active, @badge_no WHILE @@FETCH_STATUS = 0 
BEGIN 
        FETCH NEXT FROM S_CURSOR INTO @Id, @name, @password, @dept, @division, @active, @badge_no
	     INSERT INTO  stemp(ID, name, password, dept, division, active, badge_no)
	     VALUES (@ID, @name, @password, @dept, @division, @active, @badge_no)
	     WHERE  NOT EXISTS (SELECT  i.ID  FROM nameID i WHERE i.ID = @Id)
 END

CLOSE S_CURSOR 
DEALLOCATE S_CURSOR

 

[TheWebDataGuy]

What are the relevant columns in the two tables (nameid & employees)?

 

[SonOfEmidec1100]

I am a bit confused by the logic here - In the first select statement you are selecting the ID from table nameid, then in the insert you are only inserting rows where the ID does not exist in nameid or am I just confusing myself ?

Ignoring the above - your fetch next code is not quite right, also I think theres an error in the insert statement you cant have a WHERE clause without a SELECT.

Try this

FETCH NEXT FROM S_CURSOR INTO @Id, @name, @password, @dept, @division, @active, @badge_no
         
WHILE (@@FETCH_STATUS <> -1 )
    BEGIN

INSERT INTO  stemp(ID, name, password, dept, division, active, badge_no)
         SELECT  @ID, @name, @password, @dept, @division,@active, @badge_no
         WHERE  NOT EXISTS (SELECT  i.ID  FROM nameID i WHERE i.ID = @Id)

      FETCH NEXT FROM S_CURSOR INTO @Id, @name, @password, @dept, @division, @active, @badge_no


END

 

[letoii]

OK...

TheWebDataGuy
- Question:
"What are the relevant columns in the two tables (nameid & employees)?"
- Answer:
None. t\The only relevance that I found was nameId.name & employess.lname thus the "where name like lname" clause.

SonOfEmidec1100
- Thanks for the fix in the Fetch.

To anyone mercifully responding to this post:
I was handed two tables from two different departments and was told to make one table with the relevant info.
I thought I would do it in two parts, the first updating the "nameID" table with all the employees in the "employees " table that don't exist yet, then I would alter the code and update all the rows without a "badge_no".
Not being proficient at SQL I thought it was best to break it in to parts.
Anyone that has a more efficient method I would greatly appreciate any input.

Thank You o'masters...