locked
Access Data Source Dose not like this query? RRS feed

  • Question

  • User881008188 posted

    This Query Dose NOT work in Access 2000

    SELECT (Members.First_Name + " " + Members.Last_Name)AS Member,
    (
    (SELECT Friend_E_Mail,
    FROM Friends,Members
    WHERE My_E_Mail = ? and Friend_E_Mail <> ?
    UNION ALL
    SELECT My_E_Mail,
    FROM Friends,Members
    WHERE Friend_E_Mail = ?and My_E_Mail <> ?)) AS E_Mail,
    (Members.First_Name) AS Name

    FROM Members,Friends

    Sample 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


    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.

    MY QUESTION IS : i keep getting this error "Operation Not Allowed in Sub Query" is their a work around query i can use without using a stored procedure?

    Thanks.

    Tuesday, May 14, 2013 2:35 PM

Answers

  • User1938476581 posted

    Hi,

    I'm sorry for my careless.I write duplicated 'from'. Just removing one  can resolve the error like this

    SELECT (Members.First_Name + " " + Members.Last_Name)AS Member, My_E_Mail, (Members.First_Name) AS Name
     FROM Members,Friends  
     where My_E_Mail in
           (SELECT Friend_E_Mail,
                   FROM Friends,Members
                   WHERE My_E_Mail = ? and Friend_E_Mail <> ?
            UNION ALL
            SELECT My_E_Mail,
                   FROM Friends,Members
                   WHERE Friend_E_Mail = ?and My_E_Mail <> ?)

    And about your query cold the sub query return multiple records, so it dones't work. Try above again.

    Hope it can help you

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 16, 2013 9:41 PM

All replies

  • User1938476581 posted

    Hi,

    Please check your sql query if works right. And try this

    SELECT (Members.First_Name + " " + Members.Last_Name)AS Member, My_E_Mail, (Members.First_Name) AS Name
     from FROM Members,Friends
     where My_E_Mail in
           (SELECT Friend_E_Mail,
                   FROM Friends,Members
                   WHERE My_E_Mail = ? and Friend_E_Mail <> ?
            UNION ALL
            SELECT My_E_Mail,
                   FROM Friends,Members
                   WHERE Friend_E_Mail = ?and My_E_Mail <> ?)
            
    
    
    

    Hope it can help you

    Wednesday, May 15, 2013 11:42 PM
  • User881008188 posted

    i keep getting Snytax Error in FROM clause thanks thought

    Thursday, May 16, 2013 2:55 PM
  • User1938476581 posted

    Hi,

    I'm sorry for my careless.I write duplicated 'from'. Just removing one  can resolve the error like this

    SELECT (Members.First_Name + " " + Members.Last_Name)AS Member, My_E_Mail, (Members.First_Name) AS Name
     FROM Members,Friends  
     where My_E_Mail in
           (SELECT Friend_E_Mail,
                   FROM Friends,Members
                   WHERE My_E_Mail = ? and Friend_E_Mail <> ?
            UNION ALL
            SELECT My_E_Mail,
                   FROM Friends,Members
                   WHERE Friend_E_Mail = ?and My_E_Mail <> ?)

    And about your query cold the sub query return multiple records, so it dones't work. Try above again.

    Hope it can help you

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 16, 2013 9:41 PM