R
rowerneedshelp
here's my scenario:
I have three related tables with the following fields:
id phone <---- table a
phone gender location info <-----table b
id name address gender location history<-----table c
A is related to c, which is related to b. A/id is related to C/Id. B/Phone
is related to A/Phone. There aren't the same number of records in each field.
So B has fewer records than c for example.
I run a query and get all the matching records for all these related files.
But of course I get duplicates because the phone match is a one to many.
So now I have something like this:
phone name location gender (location) (gender)
555 jim x m x m
555 jane x f a f
I want to match the gender to the gender in the two different tables. If
they aren't the same, I'll throw out that record. I can do that fine.
But now, if I have this:
phone name location gender (location) (gender)
555 jim x m x f
555 jane x f x f
555 betty x f a f
I know that the second row is the one I want because the location and gender
from the two different files match. But then I get this...
phone name location gender (location) (gender)
555 jim x m x f
555 jane x f a f
555 betty x f a f
I realize in this grouping there are two possible matches. The phone is the
same for all, but it's the location and gender that help me decide which of
the related rows I want. Now I can't figure which one is the right one, so I
want to pick the one that has the most recent value in the history field.
So my question is - how do i compare values between rows, after first
finding the groups?
I have three related tables with the following fields:
id phone <---- table a
phone gender location info <-----table b
id name address gender location history<-----table c
A is related to c, which is related to b. A/id is related to C/Id. B/Phone
is related to A/Phone. There aren't the same number of records in each field.
So B has fewer records than c for example.
I run a query and get all the matching records for all these related files.
But of course I get duplicates because the phone match is a one to many.
So now I have something like this:
phone name location gender (location) (gender)
555 jim x m x m
555 jane x f a f
I want to match the gender to the gender in the two different tables. If
they aren't the same, I'll throw out that record. I can do that fine.
But now, if I have this:
phone name location gender (location) (gender)
555 jim x m x f
555 jane x f x f
555 betty x f a f
I know that the second row is the one I want because the location and gender
from the two different files match. But then I get this...
phone name location gender (location) (gender)
555 jim x m x f
555 jane x f a f
555 betty x f a f
I realize in this grouping there are two possible matches. The phone is the
same for all, but it's the location and gender that help me decide which of
the related rows I want. Now I can't figure which one is the right one, so I
want to pick the one that has the most recent value in the history field.
So my question is - how do i compare values between rows, after first
finding the groups?