> I would like to search a table and then search the results of the original
> search, but my syntax is not correct:
>
> Select * from tempERCP,
> (
> Select * from tempERCP    //get a subset of all records
> Where
> Diagnosis like '%pseudocyst%'
> or
> Diagnosis2 like '%pseudocyst%'
> or
> Diagnosis3 like '%pseudocyst%'
> ),
> (
> Select * from tempERCP   //get a subset of the first subset of all records
> where
> Pancreas not like '%fistula%'
> or
> Pancreas2 not like '%fistula%'
> or
> Pancreas3 not like '%fistula%'
> or
> Pancreas not like '%cavity%'
> or
> Pancreas2 not like '%cavity%'
> or
> Pancreas3 not like '%cavity%'
> or
> Pancreas not like '%other%'
> or
> Pancreas not like '%other%'
> or
> Pancreas not like '%other%')
>
>
> Have tried all sorts of permutations to no avail.

"Hilarion" <hilari0n@noemail.nospam> wrote in message
news:evSI4Me0GHA.1304@TK2MSFTNGP05.phx.gbl...
> harry wrote:
>
>> I would like to search a table and then search the results of the
>> original search, but my syntax is not correct:
>>
>> Select * from tempERCP,
>> (
>> Select * from tempERCP    //get a subset of all records
>> Where
>> Diagnosis like '%pseudocyst%'
>> or
>> Diagnosis2 like '%pseudocyst%'
>> or
>> Diagnosis3 like '%pseudocyst%'
>> ),
>> (
>> Select * from tempERCP   //get a subset of the first subset of all
>> records
>> where
>> Pancreas not like '%fistula%'
>> or
>> Pancreas2 not like '%fistula%'
>> or
>> Pancreas3 not like '%fistula%'
>> or
>> Pancreas not like '%cavity%'
>> or
>> Pancreas2 not like '%cavity%'
>> or
>> Pancreas3 not like '%cavity%'
>> or
>> Pancreas not like '%other%'
>> or
>> Pancreas not like '%other%'
>> or
>> Pancreas not like '%other%')
>>
>>
>> Have tried all sorts of permutations to no avail.
>
> I'm not sure if I understand what you want to achieve.
> If it's like that:
>
> 1. we have a query like this:
>   SELECT *
>   FROM some_table
>   WHERE first_filtering_expressions
> 2. we want to get a subset of previous results with some other
>   filtering expressions applied, then you can do it
>   in (at least) two ways:
>   A:
>   SELECT *
>   FROM some_table
>   WHERE first_filtering_expressions
>     AND second_filtering_expressions
>   B:
>   SELECT *
>   FROM (
>     SELECT *
>     FROM some_table
>     WHERE first_filtering_expressions
>   )
>   WHERE second_filtering_expressions
>   The "A" variant is faster and (in general) better. The "B"
>   variant let's you reuse the entire query text.
>   None of those both variants reuses real results
>   of the query executed in the first step.
>
> If you want to reuse results from the first step, then
> you could try with temporary table:
>
> CREATE TABLE #some_table_temporary_subset (
>  columns definitions here (as in "some_table")
> )
>
> INSERT INTO #some_table_temporary_subset
> SELECT *
> FROM some_table
> WHERE first_filtering_expressions
>
> SELECT *
> FROM #some_table_temporary_subset
>
> SELECT *
> FROM #some_table_temporary_subset
> WHERE second_filtering_expressions
>
> DROP TABLE TABLE #some_table_temporary_subset
>
>
> This solution allows reuse of the result but will not use
> any advantages of the oryginal table (eg. indexes) unless
> they are also created for the temporary table (which
> takes time and makes sense only if the temporary table
> gets queried many times).
>
>
> If you expected something else, then please describe
> the data you have in the table queried (it's structure
> and function and give some example data) and the
> exact result you would like to get (based on that
> example data).
>
>
> Kamil 'Hilarion' Nowicki

"harry" <mac***@musc.edu> wrote in message news:ezKns9d0GHA.3568@TK2MSFTNGP03.phx.gbl...
> Gurus
> I would like to search a table and then search the results of the original
> search, but my syntax is not correct:
>
> Select * from tempERCP,
> (
> Select * from tempERCP    //get a subset of all records
> Where
> Diagnosis like '%pseudocyst%'
> or
> Diagnosis2 like '%pseudocyst%'
> or
> Diagnosis3 like '%pseudocyst%'
> ),
> (
> Select * from tempERCP   //get a subset of the first subset of all records
> where
> Pancreas not like '%fistula%'
> or
> Pancreas2 not like '%fistula%'
> or
> Pancreas3 not like '%fistula%'
> or
> Pancreas not like '%cavity%'
> or
> Pancreas2 not like '%cavity%'
> or
> Pancreas3 not like '%cavity%'
> or
> Pancreas not like '%other%'
> or
> Pancreas not like '%other%'
> or
> Pancreas not like '%other%')
>
>
> Have tried all sorts of permutations to no avail.
>
> Thanks
> _harry
>
>

  "harry" <mac***@musc.edu> wrote in message news:ezKns9d0GHA.3568@TK2MSFTNGP03.phx.gbl...
  > Gurus
  > I would like to search a table and then search the results of the original
  > search, but my syntax is not correct:
  >
  > Select * from tempERCP,
  > (
  > Select * from tempERCP    //get a subset of all records
  > Where
  > Diagnosis like '%pseudocyst%'
  > or
  > Diagnosis2 like '%pseudocyst%'
  > or
  > Diagnosis3 like '%pseudocyst%'
  > ),
  > (
  > Select * from tempERCP   //get a subset of the first subset of all records
  > where
  > Pancreas not like '%fistula%'
  > or
  > Pancreas2 not like '%fistula%'
  > or
  > Pancreas3 not like '%fistula%'
  > or
  > Pancreas not like '%cavity%'
  > or
  > Pancreas2 not like '%cavity%'
  > or
  > Pancreas3 not like '%cavity%'
  > or
  > Pancreas not like '%other%'
  > or
  > Pancreas not like '%other%'
  > or
  > Pancreas not like '%other%')
  >
  >
  > Have tried all sorts of permutations to no avail.
  >
  > Thanks
  > _harry
  >
  >