[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

872 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