in reply to Query rewriting question
is your join logic correct? As i read it you're checking if the phone id is the same as the min for _any_ member, and not necessarily the min for _that_ member...my $min_phone_ids = $dbh->selectcol_arrayref("SELECT MIN(PhoneNumberID +) FROM phone_numbers GROUP BY MemberID"); my $sql = <<EOF; SELECT FirstName, LastName, PhoneNumber, members.MemberID AS MemberID FROM members LEFT JOIN phone_numbers ON members.MemberID=phone_numbers.MemberID EOF $sql .= " AND phone_numbers.PhoneNumberID IN (" . join(',','?' x @$mi +n_phone_ids). ") " $sql .= <<EOF; WHERE Verified=1 ORDER BY LastName, FirstName, members.MemberID EOF $dbh->selectall_arrayref( $sql, {}, @$min_phone_ids );
|
|---|