Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2002-06-07 : 01:11:21
|
| SELECT hotUserID, tblUserDetails.nameOnline from tblHotList hot JOIN tblUserDetails on hot.hotUserID = tblUserDetails.userID WHERE hot.userID = '1' ORDER BY sort ASCI'm currently running this query, and it brings back the results I would like but I need to modify it. I have another table called TBLACTIVE_USERS - that contains a column USERID that I would like to JOIN onto. How can I modify this query so the recordset brings back a NULL or a value according to whether the two columns JOIN.??I tried adding this but it makes the query bring back 0 records, which is incorrect.JOIN tblActive_Users on tblActive_Users.userID = hot.hotUserIDAlso, the reason I am doing this is because I believe it will be a performance increase of the way it is currently setup. Currently it opens a new recordset and searches for the USERID in TBLACTIVE_USERS from each value found in the first query. Am I right in this ?Thanks a bunch once again,Mike |
|
|
dataphile
Yak Posting Veteran
71 Posts |
Posted - 2002-06-07 : 03:20:50
|
| You need to do an outer join, left or right depending on which table contains null values.You also need to be aware that you 'cannot' use the field on which you join in the where clause if it is going to contain null values, unless you include the [or hot.userID is null] clause.SELECT hotUserID, tblUserDetails.nameOnline from tblHotList hot JOIN tblUserDetails on hot.hotUserID = tblUserDetails.userID [left|right] outer JOIN tblActive_Users on tblActive_Users.userID = hot.hotUserID WHERE hot.userID = '1' ORDER BY sort ASC 2.1A lot depends on the indexes and the size of the first query you had, but SQL will usually compile the query in such a way that it doesn't make a difference whether you first join two tables and then a third to the result, or three immediately, as long as you do not do an order by before you add the third table and the first two tables have a massive result set and the third table disqualifies a lot of those records. Adding three tables at the same time is just cleaner. If you want to see for yourself switch on the "show query execution plan" in query analyser. |
 |
|
|
|
|
|
JOIN - if exists