I have the following querry that works great from SQL. SELECT tbl_ITPros.FName, tbl_ITPros.LName, tbl_Courses.Title, tbl_CoursesAndIDs.MandatoryOptional FROM tbl_ITPros INNER JOIN tbl_CoursesAndIDs ON tbl_ITPros.ITProsID = tbl_CoursesAndIDs.ITProsID INNER JOIN
tbl_Courses ON tbl_CoursesAndIDs.CourseID = tbl_Courses.CourseID WHERE (tbl_ITPros.LName = 'motley') However, I am having a hard time figuring out how to understand / do the querry once I start the Where part. Thanks for any tips/clues/suggestions -- John
I don't understand - the WHERE clause looks just fine at first glance. Did you want to narrow your results down further but aren't sure how to proceed?
How you do this depends on what the data should look like when it's returned. How about posting two or three records from each of the tables in the join so we can see (just include the fields used in the query). Don
Don Kiely, MCP, MCSD
In the Last Frontier, Interior Alaska
Please post questions and replies to the forum! And remember to MARK AS ANSWER when someone definitively answers a question or resolves a problem!
I guess I did not make myself clear. The query works great but I am pulling it from SQL not the web matrix. I problem I am having is trying to figure out how to make the Inner Joins with the Web Matrix tool. I finish the select statement and then I click on
Where. At this point I get confused. The matrix only says Join, not inner join. yada yada yada. Should I just cut my SQL statement from SQL and paste in directly into my code like I would a normal ASP app or should I recreate the query with the web matrix
tool. Any tips on converting my SQL statement via the Web Matrix. Thanks !
"Should I just cut my SQL statement from SQL and paste in directly into my code like I would a normal ASP app..." Oh, no you didn't! :) In my practice over the past five years or so I have noticed about a ten-fold increase in performance by using stored procedures
instead of embedded SQL statements in your code. I won't get in to the technical explanation of why this is, but I believe it would do you a great deal of good if you because very familiar with T-SQL. I haven't used a query designer since Access 95 -- writing
your own T-SQL can be a great thing (if you know what you're doing). I would create a SqlCommand object that calls a stored procedure with your variables as input parameters.
I hear what you are saying and have always wanted to learn how to do it. However, I have no clue. Can you point me in the direction or post a code sample. Right now I would do something like this. Set RecordCollection = CTDB.Execute("Select CompleteName from
tbl_ITpros where ITprosID = '" & session ("SupervisorID") & "'") How would you do it. Thanks --
Well I use SQL Server, so mine would be something like this (with the SqlClient namespace):
Dim conn As New SqlConnection(MyConnectionString)
Dim cmd As New SqlCommand("g_CompleteName", conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@ITProsID", SqlDbType.Int).Value = Session("SupervisorID")
Dim sdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
If you remove the code to create the connection and command, you're only looking at 1 or 2 extra lines but the performance gain you can reap can be huge.
Jmotley
Member
115 Points
23 Posts
Help with Inner Joins
Aug 12, 2003 08:59 PM|LINK
russnem
Contributor
7001 Points
1389 Posts
ASPInsiders
MVP
Re: Help with Inner Joins
Aug 13, 2003 03:51 AM|LINK
donkiely
All-Star
15929 Points
2518 Posts
ASPInsiders
Moderator
MVP
Re: Help with Inner Joins
Aug 13, 2003 05:45 AM|LINK
In the Last Frontier, Interior Alaska
Please post questions and replies to the forum! And remember to MARK AS ANSWER when someone definitively answers a question or resolves a problem!
Jmotley
Member
115 Points
23 Posts
Re: Help with Inner Joins
Aug 13, 2003 01:17 PM|LINK
russnem
Contributor
7001 Points
1389 Posts
ASPInsiders
MVP
Re: Help with Inner Joins
Aug 13, 2003 03:20 PM|LINK
Jmotley
Member
115 Points
23 Posts
Re: Help with Inner Joins
Aug 13, 2003 06:43 PM|LINK
russnem
Contributor
7001 Points
1389 Posts
ASPInsiders
MVP
Re: Help with Inner Joins
Aug 13, 2003 08:29 PM|LINK
Dim conn As New SqlConnection(MyConnectionString) Dim cmd As New SqlCommand("g_CompleteName", conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@ITProsID", SqlDbType.Int).Value = Session("SupervisorID") Dim sdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)If you remove the code to create the connection and command, you're only looking at 1 or 2 extra lines but the performance gain you can reap can be huge.Jmotley
Member
115 Points
23 Posts
Re: Help with Inner Joins
Aug 13, 2003 08:43 PM|LINK
russnem
Contributor
7001 Points
1389 Posts
ASPInsiders
MVP
Re: Help with Inner Joins
Aug 14, 2003 01:09 AM|LINK