Select Where More Than 1 Value Exists

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 2005 Forums

Transact-SQL (2005)

Select Where More Than 1 Value Exists

Author

Topic

sgmorse
Starting Member

5 Posts

Posted - 2007-05-23 : 13:03:13

Hello,

I hope someone can help me with this. It seems fairly simple but I just can't make it work.

I need to create a recordset that displays information from 2 tables when a field in 1 table contains more than 1 like value.

For example if the code field has 3 values that are 110, then display those three records, but not the rest.

This is what I've got so far but it isn't working:

select t.company_id, t.employee_id,t.oth_hrs_cd, c.earn_desc,t.per_end_date
from PUB.tran_hst as t, PUB.co_earn as c
join (select t.oth_hrs_cd, count (*)
from PUB.tran_hst as t
WHERE oth_hrs_cd <> ''
group by oth_hrs_cd
having count (*) > 1) as d on t.oth_hrs_cd = d.oth_hrs_cd
WHERE t.company_id = c.company_id AND t.oth_hrs_cd = c.earn_type AND t.employee_id = 'xxxxxx' AND t.per_end_date = 'xx/xx/xxxx'

Any help would be appreciated.

Scott Morse

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-23 : 13:03:50

Can you post some sample data in the tables and expected output?

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sgmorse
Starting Member

5 Posts

Posted - 2007-05-23 : 13:11:59

An example output is below:

company_id employee_id oth_hrs_cd earn_desc per_end_date
xxxxx xxxxxx 110 Overtime xx/xx/xxxx
xxxxx xxxxxx 110 Overtime xx/xx/xxxx
xxxxx xxxxxx 110 Overtime xx/xx/xxxx
xxxxx xxxxxx 100 Regular xx/xx/xxxx

I would expect the query to return the three 110 codes.

Scott Morse

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-23 : 13:24:30

and sample data from the tables too..

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sgmorse
Starting Member

5 Posts

Posted - 2007-05-23 : 13:34:30

Thanks.

Let me know if this doesn't provide you with enough information:

tran_hst
company_id employee_id oth_hrs_cd per_end_date
xxxxx xxxxxx 110 xx/xx/xxxx
xxxxx xxxxxx 110 xx/xx/xxxx
xxxxx xxxxxx 110 xx/xx/xxxx
xxxxx xxxxxx 100 xx/xx/xxxx

co_earn
company_id earn_type earn_desc
xxxxx 110 Overtime
xxxxx 110 Overtime
xxxxx 110 Overtime
xxxxx 100 Regular

Scott Morse

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-23 : 13:40:32

Since you masked all most of the data I am unable to test out the query you have, but looking at it you probably need an EXISTS instead of join.
so you can


SELECT ...
FROM..
WHERE EXISTS ( SELECT.. GROUP BY ... HAVING Count(*) > 1)

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sgmorse
Starting Member

5 Posts

Posted - 2007-05-23 : 13:56:08

Thanks Dinakar, but I'm accessing a Progress database with an ODBC driver that doesn't support the EXISTS condition.

Scott Morse

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-23 : 14:00:11

ahh..not sure if you can get help in these forums as everything here is SQL Server related..

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sgmorse
Starting Member

5 Posts

Posted - 2007-05-23 : 14:02:59

I suspect you are right. Thanks for your time though.

Scott Morse

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources