SQL SELECT Question

Question

I am trying to perform a SQL query that acts in two parts.

First, I have a query that returns a list of 10 Ids. But then I want to have a SELECT statement which has a WHERE clause for each of these 10 ids.

Is this possible?

I tried:

    SELECT * FROM tablenameWHERE id= (SELECT id FROM table_of_ids WHERE 
    tableid='1a177de1-3f25c9b7910b' OR 
    tableid='64faecca-133af807a65a' OR
... up to 10 Ids)

but it returns with an error stating the subquery returns more than 1 row.

Note, the tableid and id columns of table_of_ids are different values.

Does anyone know how to accomplish this? I seem to be at a loss myself.

If it matters, I am using mySQL and PHP.

Cheers, Brett

Answer 1

Not a very optimized query, but you could use IN instead of =

 SELECT * FROM tablename WHERE id IN (SELECT id FROM table_of_ids WHERE 
    tableid='1a177de1-3f25c9b7910b' OR 
    tableid='64faecca-133af807a65a' OR
... up to 10 Ids)

Answer 2

Change it to in()

WHERE id IN (SELECT id ...)

Answer 3

Replace the = with the IN keyword.

select * from sometable where key in (subselect that returns one column)

Answer 4

Looks like you can;

SELECT * 
FROM tablename
  INNER JOIN table_of_ids ON table_of_ids.id = tablename.id
WHERE table_of_ids.tableid IN (
  '1a177de1-3f25c9b7910b',
  '64faecca-133af807a65a',
... )

Answer 5

Don't use the "=" operator, use the "IN" operator. See this tutorial for examples.

Answer 6

Use IN keyword.

SELECT * FROM user WHERE Id IN(SELECT userId FROM table).

Id is primary key of your first table and userId is foreign key in second table of coz.