Solved

What is wrong with a sub query that returns multiple columns ?

Posted on 2008-06-26
1
153 Views
Last Modified: 2010-03-19
I have a problem with the following sql code:

  SELECT c.WarehouseNumber, c.Row, c.Height, c.ArticleNumber, c.EntryDate, c.State
  FROM tblWarehouseContents c
  WHERE (c.State = 0) AND (c.WarehouseNumber, c.Row, c.Height) IN
  (
    select l.WarehouseNumber, l.Row, l.Height
    from tblWarehouseLayout l INNER JOIN tblPalletArticle a
    ON l.ArticleNumber = a.ArticleNumber
    where (a.ArticleName LIKE '34.0003C' AND l.Status = 0)
  )

When executing it always generates the following error message:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.

----------

Using only one column works fine.

Any help appreciated.
Thanx in advance.
0
Comment
Question by:DocZito
1 Comment
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
Comment Utility
subqueries can only return 1 row. the EXISTS( subquery ) is the "exception", as it does not return the rows, but just a TRUE/FALSE based on the existance of rows matching.

your query shall read like this
  SELECT c.WarehouseNumber, c.Row, c.Height, c.ArticleNumber, c.EntryDate, c.State
  FROM tblWarehouseContents c
  WHERE (c.State = 0) 
    AND EXISTS 
  ( 
    select l.WarehouseNumber, l.Row, l.Height
    from tblWarehouseLayout l INNER JOIN tblPalletArticle a
    ON l.ArticleNumber = a.ArticleNumber
    where a.ArticleName LIKE '34.0003C' AND l.Status = 0
      and l.WarehouseNumber = c.WarehouseNumber
      and l.row = c.Row
      and l.Height = c.Height
  )

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now