more than one results in the subquery error

MySQL & MariaDB Other MySQL® Questions
1

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

2

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.

3

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