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

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" <lee.j.mundie@ntlworld.com> wrote in message
news:syzvb.2163$WJ.349@newsfep3-gui.server.ntli.net...

> 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
>
>

[Lee Mundie]

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

Regards
Lee

<solex> wrote in message news:%23H344DKsDHA.640@tk2msftngp13.phx.gbl...

> 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" <lee.j.mundie@ntlworld.com> wrote in message
> news:syzvb.2163$WJ.349@newsfep3-gui.server.ntli.net...

> > 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
> >
> >

>
>

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

"Lee Mundie" <lee.j.mundie@ntlworld.com> wrote in message
news:KAAvb.259$CH.249@newsfep1-gui.server.ntli.net...

> Hmmm... implimented and checked, but is returning zero records...
>
> Regards
> Lee
>
> <solex> wrote in message news:%23H344DKsDHA.640@tk2msftngp13.phx.gbl...

> > 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" <lee.j.mundie@ntlworld.com> wrote in message
> > news:syzvb.2163$WJ.349@newsfep3-gui.server.ntli.net...

> > > 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
> > >
> > >

> >
> >

>
>