subquery with 2 fields? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

subquery with 2 fields?

Author

Topic

RK88
Starting Member

5 Posts

Posted - 2005-03-29 : 14:19:30

Example:

select emp_id, name, age....
from employee
where emp_id in (select ID from mgr where....)

The query is based on the result of the ID from manager table. What if I need to base on TWO conditions say: ID and Location from the mgr table? How can I write a query that only pull certain ID with certain Locations? I can go into more details if needed.

Many Thanks 8-)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-03-29 : 15:48:46

Use the WHERE clause of your subquery to limit the IDs returned...

select emp_id, name, age....
from employee
where emp_id in (select ID from mgr where Location = 'Wales')

HTH

=================================================================
In order to improve the mind, we ought less to learn than to contemplate.
-Rene Descartes, philosopher and mathematician (1596-1650)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-29 : 15:53:09

Using an INNER JOIN to the SELECT statement or table will allow you to filter the data as needed. You can join on as many columns as necessary.

- Jeff

andy8979
Starting Member

36 Posts

Posted - 2005-03-29 : 17:43:56

you can solve your problem by using the inner join and it will also give you a performance benifit over the in clause.

Andy

RK88
Starting Member

5 Posts

Posted - 2005-03-29 : 18:40:16

Thank you all for trying to help me out. I'm sorry for not explaining it to you clearly. I don't really understand the problem myself :(

Yes, I can find each of the duplicates if I hard-coded it. But their are about 2500 rows of duplicates.(I found that out by using a complex query with count, having, etc...). This is the actual code.

Select a.procCode_id, a.units
From Fee_Schedule_procCode a
Where a.subcode is null
and a.uniquecode is null
And a.feeschedule_id in (select distinct b.feeschedule_id
from fee_schedule b
where b.state_code not in ('PA','NY'))
group by a.procCode_id, a.units
having count(a.procCode_id) > 1
order by a.procCode_id

This query returns any procCode_id with a particular units and the count > 2 (that's how I know these procCode_id has dups)

The field I need is also the same table, feeschedule_id. But when I try to put it in, it returns 0 rows. Some how I can't add that field in the query.(I can add other fields though).

Thanks so much.

ijprasad
Starting Member

29 Posts

Posted - 2005-03-30 : 05:55:12

concenitate field and try

Inderjeet

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources