Query problem.

[Mike]

column 1 column2 column 3 column 4

1 current 1.0 yr1
1 previous 1.2 yr0
2 current 1.3 yr1
2 previous 2.0 yr0
3 previous 1.4 yr0
4. current 2.0 yr1
4. previous 1.5 yr0
.................................................


I want to use a query to return the following, basically, if there is no
"current", then it shouldn't return "previous" .
is it possible?


column 1 column2 column 3 column 4

1 current 1.0 yr1
1 previous 1.2 yr0
2 current 1.3 yr1
2 previous 2.0 yr0
4. current 2.0 yr1
4. previous 1.5 yr0

[Vishal Parkar]

try this, should work (untested though)

select a.* from table a inner join
(select column1 from table where column2 in('current', 'previous')
group by column1 having count(*) >= 2) b
on a.column1= b.column1

--
-Vishal

"Mike" <pearl_77@hotmail.com> wrote in message
news:#tTy7tyRDHA.940@TK2MSFTNGP11.phx.gbl...

> column 1 column2 column 3 column 4
>
> 1 current 1.0 yr1
> 1 previous 1.2 yr0
> 2 current 1.3 yr1
> 2 previous 2.0 yr0
> 3 previous 1.4 yr0
> 4. current 2.0 yr1
> 4. previous 1.5 yr0
> ................................................
>
>
> I want to use a query to return the following, basically, if there is no
> "current", then it shouldn't return "previous" .
> is it possible?
>
>
> column 1 column2 column 3 column 4
>
> 1 current 1.0 yr1
> 1 previous 1.2 yr0
> 2 current 1.3 yr1
> 2 previous 2.0 yr0
> 4. current 2.0 yr1
> 4. previous 1.5 yr0
>
>

[Anith Sen]

You can do this in many ways, using self joins, derived tables or even with
subqueries like:

SELECT *
FROM tbl
WHERE EXISTS( SELECT *
FROM tbl t1
INNER JOIN tbl t2
ON t1.col1 = t2.col1
AND t1.col1 = tbl.col1
WHERE t1.col2 = 'current'
AND t2.col2 = 'previous' ) ;

--
- Anith
( Please reply to newsgroups only )

[Mike]

okay... but how about this,
if there is current but no previous, then we should not eliminate
current.
is that possible?


"Anith Sen" <anith@bizdatasolutions.com> wrote in message
news:uUs$U0yRDHA.1624@tk2msftngp13.phx.gbl...

> You can do this in many ways, using self joins, derived tables or even

with

> subqueries like:
>
> SELECT *
> FROM tbl
> WHERE EXISTS( SELECT *
> FROM tbl t1
> INNER JOIN tbl t2
> ON t1.col1 = t2.col1
> AND t1.col1 = tbl.col1
> WHERE t1.col2 = 'current'
> AND t2.col2 = 'previous' ) ;
>
> --
> - Anith
> ( Please reply to newsgroups only )
>
>

[Anith Sen]

Just remove the last condition in the WHERE clause like:

SELECT *
FROM tbl
WHERE EXISTS( SELECT *
FROM tbl t1
INNER JOIN tbl t2
ON t1.col1 = t2.col1
AND t1.col1 = tbl.col1
WHERE t1.col2 = 'current' ) ;

--
- Anith
( Please reply to newsgroups only )