[thelist] SQL Query Question

[rudy]

> SELECT tblFOO.name, tblFOO.phone
 >   FROM tblFOO INNER JOIN tblBAR
>     ON (tblFOO.phone <> tblBAR.phone)

hi jay

the above query joins rows but throws away only those results where the
phone numbers match (it keeps the rows where thy are not equal) -- not what
you want, because it leaves you with nonsense matches

you were on the right track with an outer join, which is a join where all
the rows of one table are returned, regardless of whether they have a match
in the other table

so, using your data, you want an outer join on phone numbers

| 1  | John | 1115551212 | 1115551212  |
| 2  | Jane | 2225551212 |
| 3  | Josh | 3335551212 |  3335551212  |
| 4  | Joan | 4445551212 |
| 5  | Jake | 5555551212 |

and then simply throw away the ones which matched an exempt number

   select tblFOO.ID, Name, Phone
     from tblFOO outer join tblBAR
          on Phone = ExemptPhone
    where ExemptPhone is null

in an outer join, rows where there was no match have nulls in the
corresponding column


rudy