Solved

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

Posted on 2008-06-26
1
155 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

776 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