• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 459
  • Last Modified:

Adding counts in subquery

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
jlhay
Asked:
jlhay
  • 2
1 Solution
 
Anthony PerkinsCommented:
>>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
 
jlhayAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
jayaprakashbindiCommented:


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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now