SQL Filtering

[tom.exley]

I am currently filtering a table from a database using the SELECT FROM sql command. Does anyone know how I can then filter the results from the first query to produce another set of results?

Can this be done with the SELECT INTO command and if so how do you use this command?

Thanks
Tom

[Tonatiuh]

May be you can write a subquery:

SELECT * FROM table2 WHERE fieldX = (SELECT FieldY FROM table1)

But, fieldX and fieldY must be of the same type.

If you explain more details maybe I can do more explicit.

Good Look!

[Gaffer]

Depends on what database you’re using really.

If Access, you create your first SELECT FROM statement and save as a query (e.g. qryFirst). Then build your second SELECT FROM based on the query you have just created (e.g.
SELECT * FROM qryFirst WHERE qryFirst.field1 LIKE ‘k*’)

If SQL Server, (or anything based on Trans-Act SQL), you avoid having to create a new table by nesting SELECT statements.

For example;

SELECT field1, field2 FROM (SELECT * from tblTable)

This is simple logic to the Access example.

Hoep this clarifies,

[Gaffer]

Tom,

Please be aware the two examples posted by myself and Tonatiuh

are different. My example takes the original table, filters it, and filters again.
The other example from Tonatiuh queries a table using criteria based on the results of a query on another separate table.

Both are valid, but give different results…

Gaffer

[paulw]

Don't forget that you can use IN

SELECT Field2 FROM Table2 WHERE Field2 IN (SELECT Field1 FROM Table1);

That construct can be quite useful.

Cheers,

Paul.

[Edneeis]

You can also filter it.
open the recordset as SELECT Field2 FROM Table2 WHERE Field2 and then rs.filter="Field1='Somethingelse'".