2 into 1 SQL?

[Lee Mundie]

Hi,
Wondering if somebody can help me join these two statements to one!
The first looks at a tblMembers with tblBuddyList and matches the data by
Members.Author_ID and tblBuddyList.Buddy_ID etc.

really looking to get all the info in one; ultimately I want to check that
the tblBuddyList.Buddy_ID against tblActiveUser.Author_ID
to see if they match i.e. the user is online now

does that make sense! hope so, as my head suffered... lol

(1st Statement)
strSQL = "SELECT tblBuddyList.*, Members.Username, Members.Author_ID "
strSQL = strSQL & "FROM Members INNER JOIN tblBuddyList ON Members.Author_ID
= tblBuddyList.Buddy_ID "
strSQL = strSQL & "WHERE tblBuddyList.Author_ID=" & lngLoggedInUserID & "
AND tblBuddyList.Buddy_ID <> 2 "
strSQL = strSQL & "ORDER BY Members.Username ASC;"


(2nd statement)
strSQL = "FROM Members INNER JOIN tblActiveUser ON tblBuddyList.Buddy_ID =
tblActiveUser.Author_ID"


Will I have trouble diffentiating between the Member.Author_ID and
ActiveUser.Author_ID both being Author_ID - how could I get each value
seperately?

Thank in Advance...

Regards

Lee

 

[Guest]

Lee,

You might want to try something like below. In general I do not select *
from a table as you should know exactly what you are expecting and in what
order in addition, to minimize network traffic you should select only whay
you need.

No worry about identical names SQL server will resolve them into separate
columns just refer to them by the ordinal number or use the AS statement to
rename the column (e.g. SELECT au.Author_ID AS ActiveUserAuthorID).

The statment below give you everything in one statemnt the key is the LEFT
JOIN which basically states that you should give me everthing in
tblBuddyList regardless of what is in tblActiveUsers. To see if the user is
logged in just check to see if ActiveUserAuthorID IS NOT NULL.

Good Luck,
Dan




SELECT m.UserName,
m.Author_ID,
au.Author_ID AS ActiveUserAuthorID
FROM tblBuddyList bl
INNER JOIN Members m ON bl.Buddy_ID = m.AuthorID
LEFT JOIN tblActiveUser au ON bl.Buddy_ID = au.Author_ID
WHERE bl.AuthorID = @LoggedInUserID
AND bl.Buddy_ID <> 2
ORDER BY m.Username ASC;

 

[Lee Mundie]

Hmmm... implimented and checked, but is returning zero records...

Regards
Lee

 

[Guest]

Lee,
I could only use what you have given me. If the query is returning zero
records you either have a bad LoggedInUserID, all of the Buddy_IDs are equal
to 2 , or more likely there is no match between the bl.BuddyID and
au.Author_ID

If you could post the DLL and some sample data I will look at it further.

Dan