[thelist] SQL Query Question
rudy
r937 at interlog.com
Mon Apr 15 15:22:50 CDT 2002
> 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
More information about the thelist
mailing list