locked
How to write sql subqueries RRS feed

  • Question

  • User629862624 posted

    Can anyone help me out.
     
    I want to select the "productid" for all products from a table called "products" that both "user1" and "user2" have purchased

    I am using 

    ALTER PROCEDURE test
    (@user1 as int, @user2 as int)

    AS

    Select  productid from (Select productId
                From products
                Where productid=@user1)
               
    WHERE productid=@user2

    I am getting an incorrect syntax around the word "Where" message.

    Can anyone tell me the correct syntax for this

    Many thanks

    Martin

    Tuesday, September 2, 2014 11:19 PM

All replies

  • User466733699 posted

    hi, I think you should change your procedure a little bit like below

    ALTER PROCEDURE test
    (@user1 as int, @user2 as int)
    
    AS
    
    Select  productid from (Select productId
                From products
                Where productid=@user1) a
                
    WHERE productid=@user2

    Wednesday, September 3, 2014 12:25 AM
  • User2103319870 posted

    I want to select the "productid" for all products from a table called "products" that both "user1" and "user2" have purchased

    You can try with the below code

    ALTER PROCEDURE test
    (@user1 as int, @user2 as int)
    
    AS
    Select productId
                from products
    Where productid=@user1 OR productid=@user2

    Wednesday, September 3, 2014 12:45 AM
  • User629862624 posted

    Thank you for your reply

    Martin

    Wednesday, September 3, 2014 1:16 AM