Results 1 to 14 of 14

Thread: SQL Problem

  1. #1
    Join Date
    Oct 2003
    Posts
    30

    Unanswered: SQL Problem

    Hello,

    I am getting trouble executing this SQL STATEMENT

    SELECT DISTINCT Staff.Nom
    FROM Staff
    LEFT OUTER JOIN Horaire ON Staff.Nom NOT IN(SELECT Horaire.Nom From Horaire WHERE Horaire.DateVente = #" & Format(CDate(D_vente), "yyyy-mm-dd") & "#) WHERE Horaire.DateVente = #" & Format(CDate(D_vente), "yyyy-mm-dd") & "#")

    I am calling this SQL Statement in VB and MY database is Microsoft Access.

    According to me the expression NOT IN is causing the problem. I get the error message SYNTAX error in Expression.

    Anybody has an idea what is going wrong here?

    Thanks

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Re: SQL Problem

    Originally posted by mrhyman

    LEFT OUTER JOIN Horaire ON Staff.Nom NOT IN(SELECT Horaire.Nom From Horaire WHERE Horaire.DateVente = #" & Format(CDate(D_vente), "yyyy-mm-dd") & "#) WHERE Horaire.DateVente = #" & Format(CDate(D_vente), "yyyy-mm-dd") & "#")
    take out the quotes around the hashes.

  3. #3
    Join Date
    Oct 2003
    Posts
    30
    Nope.

    In fact the quotes are not the problem I think. I am trying to run an example directly in ACCESS from a Querry.

    SELECT DISTINCT Staff.Nom
    FROM Staff
    LEFT OUTER JOIN Horaire
    ON Staff.Nom NOT IN
    (SELECT Horaire.Nom FROM Horaire WHERE Horaire.DateVente =#18/08/2003#)
    WHERE Horaire.DateVente = #18/08/2003#;

    There's no quotes here as you can see and I still get syntax error.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by mrhyman
    Nope.

    In fact the quotes are not the problem I think. I am trying to run an example directly in ACCESS from a Querry.

    SELECT DISTINCT Staff.Nom
    FROM Staff
    LEFT OUTER JOIN Horaire
    ON Staff.Nom NOT IN
    (SELECT Horaire.Nom FROM Horaire WHERE Horaire.DateVente =#18/08/2003#)
    WHERE Horaire.DateVente = #18/08/2003#;

    There's no quotes here as you can see and I still get syntax error.
    That one is missing a closing paranthesis in the NOT IN statement.

  5. #5
    Join Date
    Oct 2003
    Posts
    30
    I'm sorry but could you indicate where the closing parenthesis should be? Please.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    select Staff.Nom
      from Staff
    left outer 
      join Horaire
        on Staff.Nom = Horaire.Nom 
       and             Horaire.DateVente = #18/08/2003#
     where Horaire.Nom is null
    you do not need DISTINCT on the staff name unless you have multiple people with the same name, and in that case, it's probably not a good idea to mix their results anyway

    Edit: forgot the WHERE...IS NULL because you wanted the "NOT IN" names
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2003
    Posts
    30
    The example you gave me says Join Expression not Supported.

  8. #8
    Join Date
    Oct 2003
    Posts
    30
    I don't see any missing parenthesis in the query I have.

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    n/m

  10. #10
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by mrhyman
    The example you gave me says Join Expression not Supported.
    Okay... Maybe I'm way off base with this... but I learned SQL on DB2/400... We could create OUTER joins in that... but I am sure I've read somewhere that OUTER is not supported by Microsoft Jet...

    I can't find that... but I looked up Left Outer Join in Access Help and it shows only the words LEFT JOIN (and says that it is a Left outer join) and RIGHT JOIN (and says it's a right outer join) ...

    Just for testing sake, try remove the OUTER keywords from your SQL statement and see what happens...

  11. #11
    Join Date
    Oct 2003
    Posts
    30
    Even Removing the Word OUTER in

    select Staff.Nom
    from Staff
    left join Horaire
    on Staff.Nom = Horaire.Nom
    and Horaire.DateVente = #18/08/2003#
    where Horaire.Nom is null

    I get Join EXpression not Supported

    In:

    SELECT DISTINCT Staff.Nom
    FROM Staff
    LEFT JOIN Horaire
    ON Staff.Nom NOT IN (SELECT Horaire.Nom
    FROM Horaire
    WHERE Horaire.DateVente =#18/08/2003#)
    WHERE Horaire.DateVente =#18/08/2003#

    I still get Syntax Error in Query Expression.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "join expression not supported" refers to the AND condition in the ON clause

    my bad, forgot this was the access forum

    but you cannot say JOIN..ON..NOT IN

    try this:
    Code:
    select Nom
      from Staff
     where DateVente = #18/08/2003#
       and Nom not in
           ( select Nom 
               from Horaire
              where DateVente = #18/08/2003# )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Oct 2003
    Posts
    30
    I think we are getting really close here.


    This gives me some solution which I think is the result I wanted.

    select Nom
    from Staff
    WHERE Nom not in
    ( select Nom
    from Horaire
    where DateVente = #18/08/2003# )

    What you said was

    select Nom
    from Staff
    where DateVente = #18/08/2003#
    and Nom not in
    ( select Nom
    from Horaire
    where DateVente = #18/08/2003# )

    And when I run it in Access I get a prompt for DateVente. That's because in the Table Staff there is no Datevente Column. It is in the Horaire Table that the DateVente is. But I think that removing

    where DateVente = #18/08/2003#

    and replacing it by

    WHERE Nom not in

    gives me the solution.

    Mark

    Let me check. I'll get back to you.

  14. #14
    Join Date
    Oct 2003
    Posts
    30
    OK. Confirmed. THE Solution Mentioned above works as intended. Thanks a lot to all of you.

    r937 You have been of a Great HELP.

    Thanks.

    Mark.

Posting Permissions

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