SQL Statement

[deej]

We have used a query to extract rows of data from the table peoplestats.

tablename: peoplestats
3 columns: name,age,gender

query: any male called john between age 15 and 30
typical result returned from query:

john 20 male

I now want to use the extracted data 'john' from the column 'name' and place into a variable. I then want to search a second table for any data that exactly equals this variable.


Can anyone advise?

 

[wsmall73]

A couple of ideas...

Insert your results into a temp table and then join against your other supporting tables to get the desired information.

or

use a cursor and fetch next for each name to the supporting tables for your information.

The first idea will probably be faster

 

[deej]

Thanks wsmall73
But could you give me an example of the script if I was to use the temp tables option!
Cheers

 

[wsmall73]

You could also simply join against the other table. What are you trying to obtain as a end result using this other supporting table??

 

[tlbroadbent]

You don't even need a temp table. You can JOIN a table to a query, use Not Exists or use an In clause.

Example 1: JOIN
Select t.name, t.col2, t.col3, ...
From Table2 t
Join
(Select name
From peoplestats
Where <criteria>) q
On t.name=q.name

Example 2: Not Exists
Select t.name, t.col2, t.col3, ...
From Table2 t
Where Exists
(Select * From peoplestats
Where <criteria> And name=t.name)

Example 3: In Clause
Select t.name, t.col2, t.col3, ...
From Table2 t
Where name In
(Select Name From peoplestats
Where <criteria>) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.