?
Solved

Adding counts in subquery

Posted on 2005-03-27
4
Medium Priority
?
458 Views
Last Modified: 2012-05-05
When I run this query by itself, it works fine. When I add it as a subquery, I get an error message. I'm trying to add a column that identifies those people who have only has one membership record. What's the deal with subqueries?

(SELECT b.ID
FROM Customer b INNER JOIN Membership a ON
        b.Customer = a.Customer
WHERE
EXISTS (SELECT Count(m.Membership), m.Customer
  FROM Membership m
  WHERE m.Customer = b.Customer
  GROUP BY m.Customer
  HAVING Count(m.Membership) = 1 ) )
0
Comment
Question by:jlhay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13640923
>>When I add it as a subquery, I get an error message. <<
Perhaps you can post the whole query and the error message and we may be able to give you an intelligent answer.
0
 

Author Comment

by:jlhay
ID: 13641299
Here is the full query I'm trying to do.

Select
Customer.ID,
Customer.FirstName,
Customer.LastName,
(SELECT b.ID
FROM Customer b INNER JOIN Membership a ON
        b.Customer = a.Customer
WHERE
EXISTS (SELECT Count(m.Membership), m.Customer
  FROM Membership m
  WHERE m.Customer = b.Customer
  GROUP BY m.Customer
  HAVING Count(m.Membership) = 1 )  )

FROM
Customer

The error message states 'Subquery returned more than one value. This is not permitted when the subquery follows =, < ... or when subquery is an expression.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1000 total points
ID: 13641334
Correct.  The error message is pretty self-explanatory.  Since the subquery returns more than one value you cannot use it here.  Think of it this way for each row in Customer there can be at most one row in your subquery.  You have two choices:
1.  Restrict the subquery to at most one row per customer.
2. Use something like this:
Select      Customer.ID,
      Customer.FirstName,
      Customer.LastName,
      a.ID
FROM      Customer
      Inner Join (
            SELECT      b.ID
            FROM      Customer b
                  INNER JOIN Membership a ON b.Customer = a.Customer
            WHERE      EXISTS (
                  SELECT      Count(m.Membership), m.Customer
                  FROM      Membership m
                  WHERE      m.Customer = b.Customer
                  GROUP BY m.Customer
                  HAVING      Count(m.Membership) = 1 )) A
This last will produce multiple rows for the same customer, if the derived table (your subquery) contains more than one row.
0
 

Expert Comment

by:jayaprakashbindi
ID: 13642475


One can use sub queries as above but the thing is that subqueries should return same rows as a main query

try to use any aggrgates or conditions

as a.column_name=b.cloumnname

where a is outr quey representer and b is inner query  representer


0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

752 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