# more than one results in the subquery error

**URL:** <https://forums.percona.com/t/more-than-one-results-in-the-subquery-error/1193>\
**Category:** Other MySQL® Questions\
**Created:** [June 16, 2009, 3:42pm UTC](https://forums.percona.com/t/more-than-one-results-in-the-subquery-error/1193 "2009-06-16T15:42:58Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![charanv](https://avatars.discourse-cdn.com/v4/letter/c/f1d935/32.png) [@charanv](https://forums.percona.com/u/charanv)\
**Post date:** [June 16, 2009, 3:42pm UTC](https://forums.percona.com/t/more-than-one-results-in-the-subquery-error/1193/1 "2009-06-16T15:42:58Z")

</div>

Hi Thankx in advance.

I am currently working on a query that would look something like below:

There are two tables t1 and t2 and these are the columns in each table  
t1: sid, storename  
t2. storeID, storename, keywords

Here is the query  
$sql = mysql\_query(“select t1.sid from t1,t2 where (t1.sid = t2.storeID and t2.storeID = (select t2.storeID from t2 where t2.storeID like ‘%autos%’))”);

There error I get is subquery returns more than one results. That is eaxclty how I want it to run… I have a query which pulls out couple of results I want to use these results to pull out other results from another table.

If I break up the query here is how I can write  
####################################  
METHOD2:  
$s1 = mysql\_query(“select t2.storeID from t2 where t2.keywords like ‘auto’”);  
while ($row = mysql\_fetch\_array($s1))  
{  
$id = $row[‘storeID’];  
$query\_2 = mysql\_quert(“select t1.sid from t1 where t1.sid = ‘$id’”);  
while ($row1 = mysql\_fetch\_array($query\_2))  
{  
echo $row1[‘sid’];  
}  
}

###################################

I do not want to use this method 2 where I need to write while loops. I want to know if there is a way I can write the two seperate queries as one in mysql and execute it…

Thank you  
Charan

---

<div class="post-metadata">

**Author:** ![sterin71](https://avatars.discourse-cdn.com/v4/letter/s/5e9695/32.png) [@sterin71](https://forums.percona.com/u/sterin71)\
**Post date:** [June 17, 2009, 1:31am UTC](https://forums.percona.com/t/more-than-one-results-in-the-subquery-error/1193/2 "2009-06-17T01:31:25Z")

</div>

The operator you are looking for is IN() which compares over a list:

…WHERE someID IN (SELECT id FROM someOtherTable)…

But I also advice you to use the newer INNER JOIN syntax:

SELECT t1.sidFROM t1INNER JOIN t2 ON t1.sid = t2.storeID

It’s much less error prone since the risk that you are missing a join condition is almost impossible.  
And if you start using LEFT JOIN’s then you can just hook them in seamlessly.  
The older syntax that you are using can’t be reliably mixed with LEFT JOINs due to precedence of join order between the new and older style.

---

<div class="post-metadata">

**Author:** ![charanv](https://avatars.discourse-cdn.com/v4/letter/c/f1d935/32.png) [@charanv](https://forums.percona.com/u/charanv)\
**Post date:** [June 17, 2009, 9:39am UTC](https://forums.percona.com/t/more-than-one-results-in-the-subquery-error/1193/3 "2009-06-17T09:39:09Z")

</div>

Thank you. That really helps. I will work with it.