Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    2

    Unanswered: 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
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    When you use a LEFT OUTER JOIN, you should never use "right table" columns (in this case from FBstatus ) in the WHERE clause. If you do, you will automatically exclude all records from the "Left table" that could not be matched with the right table (making it perform like an INNER JOIN ). The only exception to this rule is to test on IS NULL for a NOT NULL column (like the PK) of the Right table, to check if a match was possible. Here I added
    FBstatus.FID IS NULL
    to check for missing records.

    FBstatus.MyID <> " & Session("UserID") & " AND Starter.UserID <> " & Session("UserID") & " AND
    You should check the validity of if this condition. It "feels" wrong, but it might be correct.
    Without example data it is hard for me to say if an extra condition
    Starter.UserID = FBstatus.MyID AND
    is necessary. But I guess it is needed.

    I don't know the precedence rules about AND and OR, because I always use parentheses whenever there could be any ambiguity. It's safer and more explicit to use parentheses in those cases.

    ((Starter.Approved = 0 AND Starter.pLikes <= 25)
    OR
    Starter.Approved = 1
    )

    Code:
    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 AND
    		Starter.UserID = FBstatus.MyID AND -- ?
    		FBstatus.MyID <> " & Session("UserID") & " -- ?
    WHERE Starter.UserID <> " & Session("UserID") & " AND -- ?
    	FBstatus.FID IS NULL AND
    	((Starter.Approved = 0 AND Starter.pLikes <= 25)
    	 OR 
    	 Starter.Approved = 1
    	)
    ;
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Wim View Post
    When you use a LEFT OUTER JOIN, you should never use "right table" columns (in this case from FBstatus ) in the WHERE clause. If you do, you will automatically exclude all records from the "Left table" that could not be matched with the right table (making it perform like an INNER JOIN ).
    nicely stated


    Quote Originally Posted by Wim View Post
    I don't know the precedence rules about AND and OR, because I always use parentheses whenever there could be any ambiguity. It's safer and more explicit to use parentheses in those cases.
    i agree, always use parens when mixing ANDs and ORs

    but to satisfy your curiosity, ANDs take precedence over ORs, kinda like multiplication and division take precedence over addition and subtraction in arithmetic expressions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: Help with Left Join

    Does your result-set require values from the Starter table only?
    If so, you probably wouldn't need any JOIN with FBstatus.

    The following query will return all UserID values in Starter that do not exist in FBstatus:

    Code:
    select UserID ,FID from Starter where Starter.Approved = 1 OR Starter.Approved = 0 AND Starter.pLikes <= 25
    EXCEPT
    select MyID as UserID ,FID from FBstatus
    with the resulting query...

    Code:
    SELECT FID ,UserID ,fPath ,fCat ,pLikes ,DateCreated ,Approved
    FROM Starter s
     ,(select UserID ,FID from Starter where Starter.Approved = 1 OR Starter.Approved = 0 AND Starter.pLikes <= 25
       EXCEPT
       select MyID as UserID ,FID from FBstatus
      ) x
    WHERE x.UserID = s.UserID
    However, for a specific user, you may want to consider something like...

    Code:
    SELECT FID ,UserID ,fPath ,fCat ,pLikes ,DateCreated ,Approved
    FROM Starter
    WHERE UserID = @userid
    AND (Starter.Approved = 1 OR Starter.Approved = 0 AND Starter.pLikes <= 25)
    AND NOT EXISTS (select 'X' from FBstatus where MyID = Starter.UserID and FID = Starter.FID)
    with the resulting sqlbrowse value...

    sqlbrowse = "SELECT FID ,UserID ,fPath ,fCat ,pLikes ,DateCreated ,Approved FROM Starter WHERE UserID = " & Session("UserID") & " AND (Starter.Approved = 1 OR Starter.Approved = 0 AND Starter.pLikes <= 25) AND NOT EXISTS (select 'X' from FBstatus where MyID = Starter.UserID and FID = Starter.FID)"
    Last edited by homerow; 09-26-11 at 10:06. Reason: User UserID,FID pair and Formatting

  5. #5
    Join Date
    Sep 2011
    Posts
    2
    Quote Originally Posted by homerow View Post
    Does your result-set require values from the Starter table only?
    If so, you probably wouldn't need any JOIN with FBstatus.

    The following query will return all UserID values in Starter that do not exist in FBstatus:

    Code:
    select UserID ,FID from Starter where Starter.Approved = 1 OR Starter.Approved = 0 AND Starter.pLikes <= 25
    EXCEPT
    select MyID as UserID ,FID from FBstatus
    with the resulting query...

    Code:
    SELECT FID ,UserID ,fPath ,fCat ,pLikes ,DateCreated ,Approved
    FROM Starter s
     ,(select UserID ,FID from Starter where Starter.Approved = 1 OR Starter.Approved = 0 AND Starter.pLikes <= 25
       EXCEPT
       select MyID as UserID ,FID from FBstatus
      ) x
    WHERE x.UserID = s.UserID
    However, for a specific user, you may want to consider something like...

    Code:
    SELECT FID ,UserID ,fPath ,fCat ,pLikes ,DateCreated ,Approved
    FROM Starter
    WHERE UserID = @userid
    AND (Starter.Approved = 1 OR Starter.Approved = 0 AND Starter.pLikes <= 25)
    AND NOT EXISTS (select 'X' from FBstatus where MyID = Starter.UserID and FID = Starter.FID)
    with the resulting sqlbrowse value...

    sqlbrowse = "SELECT FID ,UserID ,fPath ,fCat ,pLikes ,DateCreated ,Approved FROM Starter WHERE UserID = " & Session("UserID") & " AND (Starter.Approved = 1 OR Starter.Approved = 0 AND Starter.pLikes <= 25) AND NOT EXISTS (select 'X' from FBstatus where MyID = Starter.UserID and FID = Starter.FID)"
    ------------------------------------------------------
    Yes, I only need records from the Starter table but the query needs to look at the FBstatus table and return only the records that are not already in the FBstatus table for that specific user.

  6. #6
    Join Date
    Sep 2011
    Posts
    71

    Please try as this code example ,and tell me the res

    SELECT tblBooks.BookNo, tblBooks.ISBN, tblBooks.Author, tblBooks.BookTitle, tblCategory.CategoryName, tblBooks.Price
    FROM (tblBooks LEFT OUTER JOIN
    tblCategory ON tblBooks.CategoryIndex = tblCategory.IndexCategory)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •