Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.
Solved
How would I write this query and not get the error, "Subquery returns more than one row"?
Posted on 2016-08-24
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?
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?
[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
6 Comments
Please try the following statement in your query syntax :
Your Current Statement
Please change to
Your Current Statement
p.id = (select pr.payment_id
Please change to
p.id in (select pr.payment_id
Active 3 days ago
>There shouldn't be more than one row in the subquery, since there's only one payment id.
Well, if you do
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.
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'
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.
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!
Active today
WHERE pr.program = 'After School'
OR pr.program = 'Night Classes'
OR pr.program = 'Camp'
OR pr.program = 'Hapkido'
OR pr.program = 'Krav Maga'
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
;
Active 2 days ago
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!
Thanks!
Featured Post
Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.
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
This article explains how to reset the password of the sa account on a Microsoft SQL Server. The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
There are times when I have encountered the need to decompress a response from a PHP request.
This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses
Course of the Month6 days, 19 hours left to enroll
704 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.