Solved

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

Posted on 2008-06-26
1
154 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
ID: 21873207
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

932 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

12 Experts available now in Live!

Get 1:1 Help Now