Select Where More Than 1 Value Exists

[sgmorse]

[FONT=Verdana, Arial, Helvetica]Posted - 05/23/2007 : 13:03:13[/FONT] [FONT=Verdana, Arial, Helvetica]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.
[/FONT]

 

[tamhas]

Without looking the code over too closely, I can say that my first inclination if it were my problem would be to rearrange it. I have found that I run into the fewest issues by following a couple of rules for where to put each part of the overall statement.

One rule is to use the WHERE clause only to select my primary records, i.e., tests only on the table which is the starting point for all of the joins.

Another is to use separate ORDER BY and GROUP BY clauses which operate on the result set, not burying these in the join as you have.

A third is to clearly and completely specify the JOIN in the FROM section in much the same fashion that I would if I had a given record in ABL and wanted to join to another record or set and what specifications I would put in the WHERE clause to get that set.

Finally, to put all of the desired result set only in the SELECT list.

I don't know if that will help, but you might try it to see if it makes it clearer what the problem is.

It might also help reading it if you posted the code in a [ pre ] .. [ /pre ] block with indentation to make it easier to follow (drop spaces in brackets).

 

[sgmorse]

Thanks for your response. Here is the correct query if it helps anyone else:

select t.company_id, t.employee_id, t.oth_hrs_cd, c.earn_desc, t.hourly_rate,t.other_hrs,t.per_end_date
from pub.tran_hst t
inner join pub.co_earn c on t.company_id = c.company_id and t.oth_hrs_cd = c.earn_type
inner join
(
select t2.employee_id, t2.per_end_date, t2.oth_hrs_cd, count(*)
from pub.tran_hst t2
where t2.oth_hrs_cd <> ''
and t2.employee_id = XXXX
and t2.per_end_date = 'XX/XX/XXXX'
group by t2.employee_id, t2.per_end_date, t2.oth_hrs_cd
having count(*) > 1
) d on t.employee_id = d.employee_id and t.per_end_date = d.per_end_date and t.oth_hrs_cd = d.oth_hrs_cd

Scott

 

[tamhas]

"Correct" as in "it now works in this form" or "correct" as in "a more clear presentation of the problem"?

 

[sgmorse]

Correct as in it works now.