Problem with sub clauses

  • I'm having issues getting the following query to work.

    "SELECT * FROM Recipe WHERE Recipe.RecipeID EXISTS (SELECT * FROM RecpCat WHERE RecpCat.RecpCatID = " & catID & ")"

    catID is being passed in as a parameter (it's an ASP page).

    The database structure looks like this:

    Table: Recipe

    RecipeID (INT)

    ...

    Table: RecpCat

    RecipeID (INT)

    RecpCatID (INT)

    The RecpCat table implements a many-to-many relationship for the recipes (a recipe can be in multiple categories).

    The error I'm getting is:

    Incorrect syntax near the keyword 'EXISTS'.

    If there's a better way to do this, please let me know; I'm not proud... 🙂

    Thanks,

    Brandon



    -Brandon

  • How about (I'm using @catID to represent the parameter... here's the basic SQL query):

    
    
    SELECT
    R.*
    FROM Recipe R
    JOIN RecpCat RC
    ON R.RecipeID = RC.RecipeID
    WHERE
    RC.RecpCatID = @catID

    K. Brian Kelley

    bkelley@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

  • Actually, I figured out why I was getting an error. I was doing a SELECT * instead of only returning one column from the subquery.

    I changed the SQL statement to read like below and it works perfectly.

    "SELECT * FROM Recipe WHERE Recipe.RecipeID EXISTS (SELECT RecipeID FROM RecpCat WHERE RecpCat.RecpCatID = " & catID & ")"

    -Brandon



    -Brandon

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply