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
Solved

How would I write this query and not get the error, "Subquery returns more than one row"?

Posted on 2016-08-24
6
94 Views
Last Modified: 2016-08-30
I've got a table that reflects a payment has recently been made and it has a id of 6558.

In an other table, I've got a column called "payment_id" and it has in the same row another column called "programs."

I want to find out in the context of a single SELECT statement whether or not the "program" that corresponds to that payment is one of several types. I wrote it like this:

select p.* from payment p where p.student_id='51' OR p.sibling_1='Kimberley Oakley' OR p.sibling_2='Kimberley Oakley' OR p.sibling_3='Kimberley Oakley' AND p.id = (select pr.payment_id from programs pr where pr.program='After School' OR pr.program='Night Classes' OR pr.program='Camp' or pr.program='Hapkido' OR pr.program='Krav Maga') order by p.payment_date DESC LIMIT 1

So, I'm looking for one payment in the payment table and I want to ensure that the only payments returned in my recordset were payments for either the "After School" program, the "Night Classes" program, the "Camp" program, "Hapkido" and "Krav Maga."

There shouldn't be more than one row in the subquery, since there's only one payment id. What am I doing wrong?
0
Comment
Question by:brucegust
6 Comments
 
LVL 2

Accepted Solution

by:
Kyaw Wanna earned 250 total points
ID: 41769700
Please try the following statement in your query syntax :

Your Current Statement
p.id = (select pr.payment_id 

Open in new window


Please change to
p.id in (select pr.payment_id 

Open in new window

1
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41769804
>There shouldn't be more than one row in the subquery, since there's only one payment id.
Well, if you do  
select pr.payment_id from programs pr where pr.program='After School' OR pr.program='Night Classes' OR pr.program='Camp' or pr.program='Hapkido' OR pr.program='Krav Maga'

Open in new window

Doesn't that have 5 IDs?

You can't compare 1 ID with 5 IDs, that's what the error is about, the subquery, not p.id, you simply ignore what the error states, it's totally clear about what is wrong.

You can use the IN operation, as Kyaw Wanna said, you can test, whether one ID is in a list of 5 IDs.

No points for me, please, give them to him.

Bye, Olaf.
0
 
LVL 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 100 total points
ID: 41770067
There shouldn't be more than one row in the subquery, since there's only one payment id.
You might want to use some kind of tool like phpMyAdmin to inspect the table.  Is the payment id marked UNIQUE?  Or is it an AUTO_INCREMENT key?  If not, you may be looking at data pollution.

If it is UNIQUE, you might move your ORDER clause into the subquery.  If you need a better answer, please show us the table structure and the test data, then we can give you a tested-and-working example!
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 total points
ID: 41770105
WHERE pr.program = 'After School'
                  OR pr.program = 'Night Classes'
                  OR pr.program = 'Camp'
                  OR pr.program = 'Hapkido'
                  OR pr.program = 'Krav Maga'

Open in new window

From just that where clause it seems that there are (at least) 5 possible programs so 5 possible rows could be returned. Even if all rows have the same payment_id there is still the possibility of more than one row. It is the number of rows being greater than one that causes the error message.

There are a variety of remedies. Using IN(...) has already been suggested and is probably the easiest to implement. Another approach is to use EXISTS, e.g.
SELECT
      p.*
FROM payment p
WHERE p.student_id = '51'
      OR p.sibling_1 = 'Kimberley Oakley'
      OR p.sibling_2 = 'Kimberley Oakley'
      OR p.sibling_3 = 'Kimberley Oakley'
      AND EXISTS (
            SELECT 1  
            FROM programs pr
            WHERE (pr.program = 'After School'
                  OR pr.program = 'Night Classes'
                  OR pr.program = 'Camp'
                  OR pr.program = 'Hapkido'
                  OR pr.program = 'Krav Maga')
                  AND pr.payment_id =  p.id
      )
ORDER BY
      p.payment_date DESC
LIMIT 1
;

Open in new window

2
 
LVL 2

Assisted Solution

by:neehar gollapudi
neehar gollapudi earned 50 total points
ID: 41770947
Did you try using cross apply( select top 1 id)
0
 

Author Comment

by:brucegust
ID: 41776368
Guys, I wound up accomplishing what needed to be done by breaking the query down into a series of if statements. Rather than trying to do all of the heavy lifting in the context of one SELECT, I just narrowed things down incrementally through a couple of if statements. Bottom line: It worked and the project is out the door, but I will keep your counsel written down. No doubt this will come up again.

Thanks!
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

860 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