I have a relationship between two tables. The two tables PKs are int types.
In one table (UserS), I need to supply the Username and get the corresponding ID (which is the PK). This is the standard ASP.NET user table when using forms authentication.
However, in a related table, I want to supply the ID I find from the Users table to get a value out.
Something like:
Run query to get ID for a username (simple select/where query) Return the result Run a subquery where I can pass in the above result - Get value where ID = result
This sounds a lot like dynamic sql. However, there might be a better suited and appropriate way of writing this query (on Sql Server 2k5).
How can I go about doing this and what gotchas lurk?
EDIT: I will try something along the lines of http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1
EDIT: Thanks for the tips everyone, I wrote this:
SELECT Discount.DiscountAmount FROM Discount INNER JOIN aspnet_Users ON Discount.DiscountCode = aspnet_Users.UserId And aspnet_Users.Username = 's'
Where 's' is to be replaced by a parameter.
Thanks