2 into 1 SQL?

Discussion in 'ASP General' started by Lee Mundie, Nov 22, 2003.

  1. Lee Mundie

    Lee Mundie Guest

    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, Nov 22, 2003
    #1
    1. Advertising

  2. Lee Mundie

    Guest 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" <> wrote in message
    news:syzvb.2163$...
    > 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, Nov 22, 2003
    #2
    1. Advertising

  3. Lee Mundie

    Lee Mundie Guest

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

    Regards
    Lee

    <solex> wrote in message news:%...
    > 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" <> wrote in message
    > news:syzvb.2163$...
    > > 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, Nov 22, 2003
    #3
  4. Lee Mundie

    Guest 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

    "Lee Mundie" <> wrote in message
    news:KAAvb.259$...
    > Hmmm... implimented and checked, but is returning zero records...
    >
    > Regards
    > Lee
    >
    > <solex> wrote in message news:%...
    > > 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" <> wrote in message
    > > news:syzvb.2163$...
    > > > 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, Nov 22, 2003
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. dna
    Replies:
    1
    Views:
    1,270
  2. Harry Zoroc
    Replies:
    1
    Views:
    928
    Gregory Vaughan
    Jul 12, 2004
  3. ecoolone
    Replies:
    0
    Views:
    752
    ecoolone
    Jan 3, 2008
  4. Jules
    Replies:
    6
    Views:
    149
    Jules
    Jul 15, 2003
  5. Belinda
    Replies:
    4
    Views:
    354
    Bob Barrows [MVP]
    Jun 11, 2004
Loading...

Share This Page