locked
Insert statement not returning any row RRS feed

  • Question

  • User-663234556 posted

    Good day All,

     I'm having problems with an insert statement with inner joins, here's the tables structure:

    tbl_Users

    UserID Int

    UserAlias str

    tbl_Locations

    LocationCode Int

    LocationName str

    tbl_UsersInLocations

    UserID int FK

    LocationCode int FK

     Here's the Insert statement

     

    INSERT INTO UsersInLocations ( UserID, LocationCode )
    SELECT UsersInLocations.UserId, UsersInLocations.LocationCode
    FROM Locations INNER JOIN (Users INNER JOIN UsersInLocations ON Users.UserId = UsersInLocations.[UserID]) ON Locations.LocationCode = UsersInLocations.LocationCode
    WHERE Users.UserId=@UserID AND Locations.LocationCode=@LocationCode;
    

     

    Thanks in advance for your help,

    Leo.

    Thursday, January 22, 2009 6:59 PM

Answers

  • User-1199946673 posted

    Thanks for your reply hans, the thihng is that i want to create a bound between users and the locations the user it's assigned to, so thus means the user can be assigned to different locations, but certainly i cannot add the same user to the same location due to the keys constraint, befor doing the insert i have a code that already tells me if the user it's assigned to a location,

    In that case, it's just as simple as:

    INSERT INTO UsersInLocations ( UserID, LocationCode ) VALUES (@UserID, @LocationCode)
    
     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, January 24, 2009 5:57 PM

All replies

  • User193237405 posted

     What errors are you getting?

    I think this is a good syntax:

    INSERT INTO tbl_UsersInLocations ( UserID, LocationCode )
    SELECT
        UIL.UserId,
        UIL.LocationCode
    FROM
        tbl_Users AS U (nolock)
        JOIN tbl_UsersInLocations AS UIL (nolock) ON U.UserId = UIL.UserId
        JOIN tbl_Locations AS L (nolock) ON UIL.LocationCode = L.LocationCode
    WHERE
        U.UserId = @UserID
        and L.LocationCode = @LocationCode

     

    Also do you have any code checking whether this user record exists in UsersInLocations before trying to do an insert? Seems if they're primary key fields it will throw an error if you're trying to insert a value that already exists. Let me know if this helps.

    Thursday, January 22, 2009 8:33 PM
  • User-663234556 posted

    Thanks for your reply Rampidbyter, it's not working, the query says that 0 rows are going to be inserted

    Friday, January 23, 2009 8:09 PM
  • User193237405 posted

    Seeing as the previous joined off the table we're trying to insert into i'd imagine this would be a better approach.

    IF NOT EXISTS(SELECT 1 FROM tbl_UsersInLocations (nolock) WHERE UserId = @UserID and LocationCode = @LocationCode)

    and EXISTS (SELECT 1 FROM tbl_Users (nolock) WHERE UserId = @UserID)

    and EXISTS (SELECT 1 FROM tbl_Locations (nolock) WHERE LocationCode = @LocationCode)

    BEGIN

    INSERT INTO tbl_UsersInLocations (@UserID, @LocationCode)

    END

     

    Friday, January 23, 2009 9:26 PM
  • User-1199946673 posted

    What exactly are you trying to achieve? It looks to me that you're trying to create duplicate records! What if you try to execute the select statement, does this return any records? If not, that's the reason why no records are inserted... if so, why do you want to insert the same record again? And if you gace a constraint on userID and locationcode, the duplicate record will not be created also...

    Friday, January 23, 2009 10:52 PM
  • User-1199946673 posted

    IF NOT EXISTS(SELECT 1 FROM tbl_UsersInLocations (nolock) WHERE UserId = @UserID and LocationCode = @LocationCode)

    and EXISTS (SELECT 1 FROM tbl_Users (nolock) WHERE UserId = @UserID)

    and EXISTS (SELECT 1 FROM tbl_Locations (nolock) WHERE LocationCode = @LocationCode)

    BEGIN

    INSERT INTO tbl_UsersInLocations (@UserID, @LocationCode)

    END

    This is an Access forum, so this will certainly not work!

    Friday, January 23, 2009 10:52 PM
  • User-663234556 posted

    Is this a stored procedure?, i'm using access 2007, so i don't think i can use it, unless i make it a function on a class (I guess).

    Please let me know how to proceed with your approach.

    Thanks again for your help,

    Leo.

    Friday, January 23, 2009 11:10 PM
  • User-1199946673 posted

    Like I already said, Roberts approach doesn't work, since it is a SQL Server Stored Procedure.

    I also asked what exactly you're trying to achieve (in words), because it will only result in duplicate records or no records at all?

    Saturday, January 24, 2009 6:36 AM
  • User-663234556 posted

    Thanks for your reply hans, the thihng is that i want to create a bound between users and the locations the user it's assigned to, so thus means the user can be assigned to different locations, but certainly i cannot add the same user to the same location due to the keys constraint, befor doing the insert i have a code that already tells me if the user it's assigned to a location, if returns false, then do the insert.

    I'm trying the the insert statement directly on Access, and yes, when execute the query it' doesn't return any row for inserting, also, i can execute the select statement and of course doesn't return any row if i supply parameters that doesn't exist on the table, and if i choose 1 that exist it's returned on query execution(as it's supposed to), so i don't really know what it's happening.

    Leo.

    Saturday, January 24, 2009 8:53 AM
  • User193237405 posted

    Whoops, well didn't see i was in Access forum. I just came in from the unanswered post link, and neglected to notice the area where the questions was posted. Also the query i gave wouldn't create duplicates because of the first initial NOT EXISTS check on the UsersInLocations table so it wouldn't even get to the insert unless the record didn't exist, but that the users and the location records do exist.

    Anyway, sorry any confusion caused by my T-SQL procedure!

    Saturday, January 24, 2009 12:57 PM
  • User-1199946673 posted

    Thanks for your reply hans, the thihng is that i want to create a bound between users and the locations the user it's assigned to, so thus means the user can be assigned to different locations, but certainly i cannot add the same user to the same location due to the keys constraint, befor doing the insert i have a code that already tells me if the user it's assigned to a location,

    In that case, it's just as simple as:

    INSERT INTO UsersInLocations ( UserID, LocationCode ) VALUES (@UserID, @LocationCode)
    
     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, January 24, 2009 5:57 PM
  • User-663234556 posted

    Thank you very much hans v, just perfect.(And Easy!!!!  LOL)

    Leo.

    Saturday, January 24, 2009 6:57 PM