nested queries

7 Apr 2006 1:12 AM

Fairy239

i got a question of how to use a result you achieve from a query in table
form and use the result to find other stuff form the other tables
for example
SELECT Omim_No
FROM av
WHERE Description LIKE '%LIVER%'
ORDER BY Omim_No ASC

SELECT Omim_No
FROM cs
WHERE CS_Description LIKE '%LIVER%'
OR CS_DATA LIKE '%LIVER%'
ORDER BY Omim_No ASC

SELECT Omim_No
FROM ti
WHERE Omim_Titles LIKE '%LIVER%'
ORDER BY Omim_No ASC

SELECT Omim_No
FROM ti_alt_title
WHERE Omim_Alt_Titles LIKE '%LIVER%'
ORDER BY Omim_No ASC

SELECT Omim_No
FROM tx
WHERE Omim_Text LIKE '%LIVER%'

SELECT subsnp_id,pop_id,allele_id
FROM AlleleFreqBySsPop
WHERE source LIKE '%LIVER%'
Instead of seraching for the word liver in the last table i would like to
search from the result i gotten from the first five table is that possible?

7 Apr 2006 6:39 AM

ML

Store the results from the first few queries in a temporary table. A slight
modification would be needed:

create table #<temp table name>
(
Omim_No <datatype>
,TextValue ntext --?
)

insert #<temp table name>
(
Omim_No
,TextValue
)
SELECT Omim_No as Omim_No
            ,Description as TextValue
FROM av
WHERE Description LIKE '%LIVER%'
union all
SELECT Omim_No
            ,CS_Description
FROM cs
WHERE CS_Description LIKE '%LIVER%'
OR CS_DATA LIKE '%LIVER%'
union all
SELECT Omim_No
            ,Omim_Titles
FROM ti
WHERE Omim_Titles LIKE '%LIVER%'
union all
SELECT Omim_No
            ,Omim_Alt_Titles
FROM ti_alt_title
WHERE Omim_Alt_Titles LIKE '%LIVER%'
union all
SELECT Omim_No
            ,Omim_Text
FROM tx
WHERE Omim_Text LIKE '%LIVER%'

Also consider using full-text search, it will certainly perform batter that
the LIKE operator.

ML

---
http://milambda.blogspot.com/

7 Apr 2006 8:51 AM

Fairy239

i have a data base to search from i search the data column omim no and i
have to link to the other table in th esame database through the omim no i
gotten through some of the other tables.

Show quote

"ML" wrote:

> Store the results from the first few queries in a temporary table. A slight
> modification would be needed:
>
> create table #<temp table name>
>   (
>   Omim_No <datatype>
>   ,TextValue ntext --?
>   )
>
> insert #<temp table name>
>   (
>   Omim_No
>   ,TextValue
>   )
> SELECT Omim_No as Omim_No
>             ,Description as TextValue
> FROM av
> WHERE Description LIKE '%LIVER%'
> union all
> SELECT Omim_No
>             ,CS_Description
> FROM cs
> WHERE CS_Description LIKE '%LIVER%'
> OR CS_DATA LIKE '%LIVER%'
> union all
> SELECT Omim_No
>             ,Omim_Titles
> FROM ti
> WHERE Omim_Titles LIKE '%LIVER%'
> union all
> SELECT Omim_No
>             ,Omim_Alt_Titles
> FROM ti_alt_title
> WHERE Omim_Alt_Titles LIKE '%LIVER%'
> union all
> SELECT Omim_No
>             ,Omim_Text
> FROM tx
> WHERE Omim_Text LIKE '%LIVER%'
>
>
> Also consider using full-text search, it will certainly perform batter that
> the LIKE operator.
>
> ML
>
> ---
> http://milambda.blogspot.com/

7 Apr 2006 8:58 AM

ML

The temporary table in my previous post stores the results of your queries
and can be joined on the Omim_No column to any other table where this column
is used.

What is the problem?

ML

---
http://milambda.blogspot.com/