#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    1
    Rep Power
    0

    Help with Left Join


    I have the following:

    sqlbrowse = "SELECT Starter.FID,Starter.UserID,Starter.fPath,Starter.f Cat,Starter.pLikes,Starter.DateCreated,Starter.App roved FROM Starter LEFT JOIN FBstatus ON Starter.FID = FBstatus.FID WHERE FBstatus.MyID <> " & Session("UserID") & " AND Starter.UserID <> " & Session("UserID") & " AND (Starter.Approved=0 AND Starter.pLikes<=25 OR Starter.Approved=1);"

    What im trying to do is get all of the records from Table Starter that have not already been added for the user FBstatus.MyID. There will be multiple users that will be adding to this table, so I need to be able to display all the records from the Starter table that only the specific user hasn't yet included in the FBstatus table. If a specific user has added that record it will not display when he request the page, but if another user hasnt added that record it will be displayed for him.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,378
    Rep Power
    391
    Code:
    select FID,
           UserID,
           fPath,
           fCat,
           PLikes,
           DateCreated,
           Approved
      from Starter 
     where not exists
          (select *
             from FBstatus 
            where Starter.FID = FBstatus.FID 
              and FBstatus.MyID = Starter.UserID)
       and UserID = <UserID> 
       and (Approved = 0 
       and pLikes <= 25)
        or Approved = 1
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    change this --
    Code:
       and UserID = <UserID> 
       and (Approved = 0 
       and pLikes <= 25)
        or Approved = 1
    to this --
    Code:
       and UserID = <UserID> 
       and (Approved = 0 
       and pLikes <= 25
        or Approved = 1 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo