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.
| Author |
Topic |
|
michaelmuller
Starting Member
2 Posts |
 Posted - 2009-07-01 : 12:32:01
|
| Hey all you SQL Experts,I'm getting weird results on this big join query. Can you help me? Neither of these work right. Both give incorrect count(*) values. No volunteer has more than two records in the joined tables (the left join query gives someone 16!) and 99% of the volunteers have zero records in the four "volunteer_???" tables.If you have a second to peruse, thanks!Mik------------------------------------------SELECT v.ID, v.last_name, v.first_name, count(veq.volunteer_ID) v_equip, count(vex.volunteer_ID) v_exer, count(vre.volunteer_ID) v_resp, count(vtr.volunteer_ID) v_train, count(vcr.volunteerID) v_cred, count(vat.volunteerID) v_att FROM wmmrc_vcc.Volunteers v left join wmmrc_vcc.volunteer_equipment veq on v.id = veq.volunteer_ID left join wmmrc_vcc.volunteer_exercise vex on v.id = vex.volunteer_ID left join wmmrc_vcc.volunteer_response vre on v.id = vre.volunteer_ID left join wmmrc_vcc.volunteer_training vtr on v.id = vtr.volunteer_ID left join wmmrc_vcc.credentials vcr on v.id = vcr.volunteerID left join wmmrc_vcc.tblEventAttendance vat on v.id = vat.volunteerID GROUP BY v.ID, v.last_name, v.first_nameORDER BY v_cred desc, v.last_name ASC, v.first_name ASC------------------------------------------SELECT v.ID, v.last_name, v.first_name,(select count(*) from wmmrc_vcc.volunteer_equipment veq where v.id = veq.volunteer_ID) as v_equip, (select count(*) from wmmrc_vcc.volunteer_exercise vex where v.id = vex.volunteer_ID) as v_exer, (select count(*) from wmmrc_vcc.volunteer_response vre where v.id = vre.volunteer_ID) as v_resp, (select count(*) from wmmrc_vcc.volunteer_training vtr where v.id = vtr.volunteer_ID) as v_train, (select count(*) from wmmrc_vcc.credentials vcr where v.id = vcr.volunteerID) as v_cred, (select count(*) from wmmrc_vcc.tblEventAttendance vat where v.id = vat.volunteerID) as v_att, FROM wmmrc_vcc.Volunteers v ORDER BY v_cred desc, v.last_name ASC, v.first_name ASC-Mik----------------Eschew Obfuscation |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-01 : 12:49:06
|
what does this give you?SELECT v.ID, v.last_name, v.first_name,count(distinct veq.volunteer_ID) v_equip, count(distinct vex.volunteer_ID) v_exer, count(distinct vre.volunteer_ID) v_resp, count(distinct vtr.volunteer_ID) v_train, count(distinct vcr.volunteerID) v_cred, count(distinct vat.volunteerID) v_att FROM wmmrc_vcc.Volunteers v left join wmmrc_vcc.volunteer_equipment veq on v.id = veq.volunteer_ID left join wmmrc_vcc.volunteer_exercise vex on v.id = vex.volunteer_ID left join wmmrc_vcc.volunteer_response vre on v.id = vre.volunteer_ID left join wmmrc_vcc.volunteer_training vtr on v.id = vtr.volunteer_ID left join wmmrc_vcc.credentials vcr on v.id = vcr.volunteerID left join wmmrc_vcc.tblEventAttendance vat on v.id = vat.volunteerID GROUP BY v.ID, v.last_name, v.first_nameORDER BY v_cred desc, v.last_name ASC, v.first_name ASC |
 |
|
|
michaelmuller
Starting Member
2 Posts |
Posted - 2009-07-01 : 13:15:31
|
| > what does this give you?Reality. Thanks, visakh16. You rock.-Mik----------------Eschew Obfuscation |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-01 : 13:25:17
|
| welcome |
|
|
|
|
|
|
|
|
SQL Help: left join count(*)