Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need JOIN/GROUP BY Help to replace Client-side code 1

Status
Not open for further replies.

dbleyl

Programmer
Mar 26, 2001
117
US
Hi,

I need to execute a join many times, and each time, add the results to a third table. If a record already exists in the third table, instead of adding the row, I need to add the value of one of it's fields to a 'count' field in the original table.

The join isn't a problem. It's the update. I'm trying to remove client-side logic that uses DAO to compare each record in the join recordset, like:

.Seek "=", rs!ZIP, rs!STNAME, rs!CNTYNAME

If .NoMatch Then
.AddNew
!ZIP_CODE = rs!ZIP
!State = rs!STNAME
!COUNTY = rs!CNTYNAME
!REG_COUNT = rs!Count
.Update
Else
.Edit
!REG_COUNT = !REG_COUNT + rs!Count
.Update
End If

I figure I can do the join, append the results to the new table, and run a GROUP BY on ZIP, STNAME, CNTYNAME. I have to join 100 times, so it seems wasteful to execute the group by each time, but the table would become too large if I just appended all the results of the joins, and then ran a group by. Any suggestions?
 
can you say the same thing but in two or three SQL's? I'm not familiar with VB. John Fill
ivfmd@mail.md
 
John,

I'm not sure how this would look in T-SQL, but here's a try sprinkled with pseudo-code:
Do this for every row in the recordset (rs):
FETCH a record that matches rs.ZIP, rs.STATE, rs.COUNTY.
IF FETCH can't find a record, --(FETCH returns nothing)
INSERT INTO tbl rs.ZIP, rs.STATE, rs.COUNTY, rs.COUNT

ELSE IF FETCH points to a record
UPDATE tbl SET tbl.[COUNT] = tbl.[COUNT] + rs.[COUNT]
WHERE tbl.ZIP = rs.ZIP AND tbl.STATE = rs.STATE AND tbl.COUNTY = rs.COUNTY

The idea is: For each row in the result set, look for the zip code in the table. If it can't be found, add a new row to the table. If it can be found, update the table's COUNT column for that row. Thanks for your help.
 
I think will be much faster to do it without fetching, just in two steps:
-------------------
update tbl set
tbl.COUNT = tbl.COUNT + t.COUNT
from
(
select * from rs
) t
where
tbl.ZIP = t.ZIP and
tbl.STATE = t.STATE and
tbl.COUNTRY = t.COUNTRY

insert into tbl
select ZIP, STATE, COUNTRY, COUNT from
(
select rs.*, tbl.zip x from rs right join tbl on
tbl.ZIP = t.ZIP and
tbl.STATE = t.STATE and
tbl.COUNTRY = t.COUNTRY
) where x is null John Fill
ivfmd@mail.md
 
Thanks John, I think it will work. By any chance, is:
insert into tbl
select ZIP, STATE, COUNTRY, COUNT from
(
select rs.*, tbl.zip x from rs right join tbl on
tbl.ZIP = t.ZIP and
tbl.STATE = t.STATE and
tbl.COUNTRY = t.COUNTRY
) where x is null
Considered a 'Right Anti Semi Join'?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top