Correlated SQL Help

[Bakedgoods]

I've set up a correlated SQL query but i'm not entirely sure i've got it right.

The purpose of it is to get all the unapproved hours for a TimeCode where the CodeOwner matches @username.

It gets the unapproved hours for every code rather than the ones for the UserName.

SELECT ID, TimeCode, Date, Hours, Username, Comments, Approved, 

(SELECT CodeOwner FROM lward.tblCodes WHERE tblCodes.CodeID = tblHours.TimeCode AND CodeOwner = @UserName) as CodeOwner

FROM lward.tblHours

WHERE Approved is NULL

 

[Haircut]

How does the Username field in tblHours relate to the CodeOwner field in tblCodes?
If the Username is the same as the CodeOwner you're looking for would something like this work?

SELECT ID, TimeCode, Date, Hours, Username, Comments, Approved
FROM lward.tblHours
WHERE Username = 
     (SELECT CodeOwner
      FROM tblCodes
      WHERE tblCodes.CodeId = tblHours.TimeCode
     )
AND Approved IS NULL

I'm guessing that because you have the CodeOwner as a separate field in your post it isn't quite this simple, but we need to know the relationships between the tables.

 

[Bakedgoods]

CodeOwner and UserName have nothing to do with each other.

The Username in tblHours is used for storing who is logging some hours. CodeOwner is the Owner of a paticular time code.

tblHours
ID, TimeCode, Date, Hours, Username, Comments, Approved

tblCodes
CodeID, CodeName, CodeDescription, CodeOwner, CodeCategory

The only relationship is CodeID and TimeCode, CodeID is the primary key and TimeCode is the foreign.

 

[chesterstu]

Can i ask why your not using a join, it is more performant that a sub query

 

[Bakedgoods]

When coding in ASP.net and using TableAdapters JOINS mess up the auto-generated INSERT, UPDATE and DELETE sql commands they are created.

It's a rather annoying procedure you have to go through to get them to work using JOINS.