help with a query (MS SQL 7)

[gojohnnygogogogo]

hello,
how can I create a query to compare values. ?
I have 2 tables new and old data, I want to compare the values in both the new and the old. ( using UNION is probably the best way)

I want to count records and group by a field.
then show the records if the count is different by twice as much.
eg,
count name
12 a
13 a
25 a
10 d
11 d
14 d


from the results I would only want to show the rows where the name is 'a' as it has one record that is double.

hope that makes sense.

please can anyone help ?

thanks.

 

[MeanGreen]

Try this:

select a.name from (
select name,count(*) acnt from tablea) a
join
select name,count(*) bcnt from tableb) b
on a.name = b.name
where b.bcnt >= (2*a.acnt)
or a.acnt >= (2*b.bcnt)

Hope this helps.

 

[gojohnnygogogogo]

thanks meangreen,
but I can't get this to work,
incorrect syntax near join....

 

[JamesLean]

Just a typo - need to open the parentheses:

[tt]join (
select...[/tt] --James