View previous topic :: View next topic
|
Author |
Message |
sreenivasreddyg
New User
Joined: 23 Apr 2005 Posts: 39 Location: delhi
|
|
|
|
I need to generate a report as specified below.
I have a table with multiple records for each policy number. The toal number of records should not match with the value in a column in another table. I tried using the join query as below. But it didn't work. Can someone help me in this.
select number from table1 a, table2 b
group by a.number
where count(*) != value; |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
sreenivasreddyg wrote: |
I need to generate a report as specified below.
I have a table with multiple records for each policy number. The toal number of records should not match with the value in a column in another table. I tried using the join query as below. But it didn't work. Can someone help me in this.
select number from table1 a, table2 b
group by a.number
where count(*) != value; |
What results did you get. The more you share with us the better we can help you. Is there a common key between the two tables?
If number is the common key between the two tables then try this, I haven't tested it but you can try and reply with results.
Code: |
SELECT A.NUMBER FROM (SELECT NUMBER, COUNT(*) AS RECORDS FROM TABLE1 GROUP BY NUMBER) A, TABLE2 B
WHERE A.NUMBER = B.NUMBER
AND A.RECORDS <> B.VALUE
|
|
|
Back to top |
|
|
sreenivasreddyg
New User
Joined: 23 Apr 2005 Posts: 39 Location: delhi
|
|
|
|
Hi Craq,
Yes number is the common key. I have executed your query and is working fine. Thank you so much. |
|
Back to top |
|
|
sreenivasreddyg
New User
Joined: 23 Apr 2005 Posts: 39 Location: delhi
|
|
|
|
Can I include update command to set the VALUE equal to RECORDS? My rqeuirement is to update the VALUE with teh RECORDS for each policy |
|
Back to top |
|
|
|