results from SELECT clause used in an IN clause 
Author Message
 results from SELECT clause used in an IN clause

I am trying to write a query that looks something like
this:

SELECT field1
FROM myTable
WHERE conditions(fixed parmeters) AND field2 IN (SELECT
field2 from myTable WHERE conditions(field1))

In other words, the IN clause needs to have a condition
that is a function of a field from the main SELECT
clause, but I can't seem to "pass" parameters to the IN
clause.

I've tried a few approaches that include declaring a
local variable, but they don't seem to work.

Is there another approach to this problem that I'm
missing.  Any pointers are appreciated!

-Ryan



Sun, 02 Oct 2005 05:43:52 GMT
 results from SELECT clause used in an IN clause

not exactly sure what you're looking for...perhaps, this would help...

http://www.algonet.se/~sommar/dynamic_sql.html

--
-oj
RAC v2.1 & QALite!
http://www.rac4sql.net


Quote:
> I am trying to write a query that looks something like
> this:

> SELECT field1
> FROM myTable
> WHERE conditions(fixed parmeters) AND field2 IN (SELECT
> field2 from myTable WHERE conditions(field1))

> In other words, the IN clause needs to have a condition
> that is a function of a field from the main SELECT
> clause, but I can't seem to "pass" parameters to the IN
> clause.

> I've tried a few approaches that include declaring a
> local variable, but they don't seem to work.

> Is there another approach to this problem that I'm
> missing.  Any pointers are appreciated!

> -Ryan



Sun, 02 Oct 2005 07:38:21 GMT
 results from SELECT clause used in an IN clause
If you didn't get the solution yet, post the DDL, sample data as insert,
function/condition and desired result set, may be some can help.
Falik


Quote:
> I am trying to write a query that looks something like
> this:

> SELECT field1
> FROM myTable
> WHERE conditions(fixed parmeters) AND field2 IN (SELECT
> field2 from myTable WHERE conditions(field1))

> In other words, the IN clause needs to have a condition
> that is a function of a field from the main SELECT
> clause, but I can't seem to "pass" parameters to the IN
> clause.

> I've tried a few approaches that include declaring a
> local variable, but they don't seem to work.

> Is there another approach to this problem that I'm
> missing.  Any pointers are appreciated!

> -Ryan



Sun, 02 Oct 2005 11:32:27 GMT
 results from SELECT clause used in an IN clause
Looks like you need a co-related query like this?

SELECT m.field1 FROM myTable m WHERE m.field2 IN
    (SELECT x.field2 FROM myTable x WHERE x.someField = m.field1)

This will pick results from the second table where the the condition matches
the "current" value from the other table.
If this is not what you want, maybe you can post in the DDL and some sample
data using which a more precise solution can be given.
--
HTH,
SriSamp
Please reply to the whole group only!


Quote:
> I am trying to write a query that looks something like
> this:

> SELECT field1
> FROM myTable
> WHERE conditions(fixed parmeters) AND field2 IN (SELECT
> field2 from myTable WHERE conditions(field1))

> In other words, the IN clause needs to have a condition
> that is a function of a field from the main SELECT
> clause, but I can't seem to "pass" parameters to the IN
> clause.

> I've tried a few approaches that include declaring a
> local variable, but they don't seem to work.

> Is there another approach to this problem that I'm
> missing.  Any pointers are appreciated!

> -Ryan



Sun, 02 Oct 2005 22:57:16 GMT
 results from SELECT clause used in an IN clause

Quote:

> I am trying to write a query that looks something like
> this:

> SELECT field1
> FROM myTable
> WHERE conditions(fixed parmeters) AND field2 IN (SELECT
> field2 from myTable WHERE conditions(field1))

> In other words, the IN clause needs to have a condition
> that is a function of a field from the main SELECT
> clause, but I can't seem to "pass" parameters to the IN
> clause.

> I've tried a few approaches that include declaring a
> local variable, but they don't seem to work.

> Is there another approach to this problem that I'm
> missing.  Any pointers are appreciated!

Very mixed up.  Look at this:

SELECT field1
FROM myTable mt_ONE
WHERE conditions(fixed parmeters) AND field2 IN (SELECT
field2 from myTable mt_TWO WHERE conditions(mt_TWO.field1))

That's effectively the same as what you already wrote.

It makes more sense as

SELECT field1
FROM myTable mt_ONE
WHERE conditions(fixed parmeters) AND field2 IN (SELECT
field2 from myTable mt_TWO WHERE
 conditions(mt_TWO.field2, mt_TWO.field1))

because you probably want

SELECT field1
FROM myTable mt_ONE
WHERE conditions(fixed parmeters) AND field2 IN (SELECT
field2 from myTable mt_TWO WHERE
 conditions(mt_TWO.field2, mt_ONE.field1))

Close? ;-)



Sun, 02 Oct 2005 23:00:54 GMT
 results from SELECT clause used in an IN clause
That looks like exactly what I need!  I'll experiment
later today post results.

Thanks for everyone's help...

-Ryan

Quote:
>-----Original Message-----
>Looks like you need a co-related query like this?

>SELECT m.field1 FROM myTable m WHERE m.field2 IN
>    (SELECT x.field2 FROM myTable x WHERE x.someField =
m.field1)

>This will pick results from the second table where the

the condition matches
Quote:
>the "current" value from the other table.
>If this is not what you want, maybe you can post in the
DDL and some sample
>data using which a more precise solution can be given.
>--
>HTH,
>SriSamp
>Please reply to the whole group only!



>> I am trying to write a query that looks something like
>> this:

>> SELECT field1
>> FROM myTable
>> WHERE conditions(fixed parmeters) AND field2 IN (SELECT
>> field2 from myTable WHERE conditions(field1))

>> In other words, the IN clause needs to have a condition
>> that is a function of a field from the main SELECT
>> clause, but I can't seem to "pass" parameters to the IN
>> clause.

>> I've tried a few approaches that include declaring a
>> local variable, but they don't seem to work.

>> Is there another approach to this problem that I'm
>> missing.  Any pointers are appreciated!

>> -Ryan

>.



Mon, 03 Oct 2005 00:02:59 GMT
 results from SELECT clause used in an IN clause
Success!

Thanks for everyone's help!

-Ryan



Mon, 03 Oct 2005 05:27:24 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. Is it possible to use a SELECT clause in a FROM clause

2. Using case stement in a where clause with an in clause

3. I don't know the difference of using index clause and just constraint clause

4. Comparison of results from SQL-SELECT in WHERE clause

5. False result using datetime in WHERE clause

6. Can you someway edit Query result set which used order by clause

7. Using results of SP in a WHERE Clause

8. using the result of a partial sum in a where clause

9. Order results when using the FOR XML clause in SQL Server 2000

10. Using Stored Procedure in an IN clause in a SELECT statement

11. Using expressions in a SELECT Clause

12. Using a variable in Select clause


 
Powered by phpBB® Forum Software