Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2009
    Posts
    2

    Unanswered: Selecting all records that DONT match subquery....

    Hey all...I'm having a brain cramp on this one. And obviously I'm getting the "subquery returned more than one value" error (pretty much I'm trying to eliminate ALL records from the recordset that dont match the subquery).

    Here's my SQL:
    SELECT dbo.Purchases.poTotalPrice, dbo.purchases.purchaseID, dbo.purchases.poDescription
    FROM dbo.Purchases
    WHERE dbo.Purchases.purchaseID <> (SELECT dbo.purchases.purchaseID FROM dbo.purchases INNER JOIN dbo.PurchaseFiles ON dbo.purchases.purchaseID = dbo.purchasefiles.purchaseID WHERE dbo.purchasefiles.pfiletype = 4 GROUP BY dbo.purchases.purchaseID)
    ORDER BY dbo.purchases.pototalprice DESC


    The logic: I have two tables. Purchases, and PurchaseFiles. When a user uploads a file that is relevant to a purchase, it creates a new entry in the PurchaseFiles table and references the PurchaseID. Well I'm trying to find ALL purchase IDs that don't have a filetype 4 uploaded and referenced against them.

    Unfortunately I can't just inner join the two tables and select everything that ISNT pfiletype 4 and group by purchase ID because some purchaseID's won't have any files tied to them at all.

    Does all of that make sense? I'm not sure how to go about doing that....Thanks a bunch!

    JE

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try NOT IN instead of <>
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2009
    Posts
    2
    Ohhh yes. NOT IN. I knew it was something easy I was spacing on. Thanks a ton for your help!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...or LEFT OUTER JOIN...WHERE IS NULL
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    or
    Code:
    SELECT ...
    FROM dbo.Purchases p
    WHERE not exists (
      SELECT 1 FROM dbo.PurchaseFiles f 
      where f.purchaseID = c.purchaseID 
      f.pfiletype = 4 
    )
    ...
    Saves you an extra join and a GROUP BY, which wasn't needed anyway.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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