Using IN operator with a sub-query

[gamingmouse]

I'm familiar with

SELECT *
FROM my_table
WHERE id IN (2,4,8)

But how can I do something like this:

SELECT *
FROM my_table
WHERE id IN (
  SELECT *
  FROM my_table
  WHERE x = 1
);

Please IGNORE that the subquery in the above example is totally unneeded. This is a purely snyntactical question.

Thanks,
gm

 

[PHV]

SELECT *
FROM my_table
WHERE id IN (
SELECT [!]id[/!]
FROM my_table
WHERE x = 1
);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gamingmouse]

What if the Select statement is a saved Query. That is, it does not work if I save

  SELECT id
  FROM my_table
  WHERE x = 1;

as MyQuery

and then try to do:

SELECT *
FROM my_table
WHERE id IN (MyQuery);

 

[PHV]

Seems you're playing with ms-access.
A saved query is like a VIEW in real database world, so use it as it were a TABLE.
Note: for JetSQL or Access query issues post in forum701

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886