SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    May 2013
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry [Access 2000/JET SQL] "Join Expression Not Supported" Help with this query??

    Code:
    SELECT     Members.First_Name + ' ' + Members.Last_Name AS Member, iif(NULL, Friends.My_E_Mail, Friends.Friend_E_Mail) AS E_Mail, 
                          Members.First_Name AS Name
    FROM         ((Members LEFT  JOIN
                          Friends ON Members.E_Mail = Friends.My_E_Mail AND Friends.Friend_E_Mail = ?) LEFT  JOIN
                          Friends Friends_1 ON Members.E_Mail = Friends.Friend_E_Mail AND Friends.My_E_Mail = ?)
    Whats is wrong with this?????


    Code:
    My Tables
        Members(all VARCHAR)     SOME DATA
        First_Name               Alester Jude Carl Jones
        Last_Name                A       B    C    J
        
        FRIENDS(ALL VARCHAR)
        My_E_Mail               Alester@lam.com Alester@lam.com  Alester@lam.com
        Friend_E_Mail           jude@lam.com    carl@lam.com   jones@lam.com
    
    ***NOTE Friends Table ALLOWS duplicates so jude can be on my_E_Mail but CAN NOT add alester because they are already friends.
    
    
    Desired Output if ("?" in above query is: jones@lam.com)
    
        +--------------+-----------+------------+
        |Member        |E_Mail     |  Name      |
        +---------------------------------------+
         Alester A   Alester@lam.com  Alester
    
    
    
    
    Desired Output if("?" in above query is: Alester@lam.com)
    
        +--------------+-----------+------------+
        |Member        |E_Mail     |  Name      |
        +---------------------------------------+
         Jude B        jude@lam.com  Jude
         carl C        carl@lam.com  Carl
         Jones J       jones@lam.com Jones
    
    PS the "?" are query string parameters that im passing in the "?" i know that works fine.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jesusw00t View Post
    Whats is wrong with this?????
    first, your IIF is incorrect, the first thing inside the parentheses should be a test, like foo='bar', in this case you would do IIF(Friends.My_E_Mail IS NULL, ...

    second, you are joining the members table to the friends table twice, but in the second join, you're not referencing friends_1 correctly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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