[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2008-06-26
1
Medium Priority
?
162 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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

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

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

612 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