ja********@gmail.com wrote:
The Null's come from the other team.
We dont have a choice why they are storing nulls as "Null"
and our database has a string value also like "Foo" which goes to
another team as that, but in terms of comparison these need to match.
To join the two tables on column C where one table's "Foo" equals the other
table's "Null," the two teams have a conflict *they* need to resolve. Team
One can say "Since you guys don't like our 'Null' in column C when there's no
other assigned value, we'll change it to 'diamond' instead." Team Two can
say "Great idea! Our 'Foo' isn't all that descriptive for 'none of the
above' either. We'll change it to 'diamond' too." And then *you* can use an
equality in your join where what used to be Team One's "Null" now matches
what used to be Team Two's "Foo" like this:
SELECT *
FROM tblOne INNER JOIN tblTwo ON tblOne.ColC = tblTwo.ColC;
If the two teams refuse to use the muscles between their ears, they can have
an arm wrestling contest. If Team One wins, both teams use "Null" for "none
of the above" in column C. If Team Two wins, both teams use "Foo" for "none
of the above" in column C. Hon, I don't know how else to put this but if you
try to resolve *their* conflict in *your* query, you aren't using the muscles
between your ears either. Note that I'm not saying it can't be done, only
that I don't want your query to look like you don't know what you're doing.
That doesn't mean you can't join the two tables on like columns and show only
records with "Null" in one table's column C and "Foo" in the other table's
column C, like this:
SELECT *
FROM tblOne INNER JOIN tblTwo ON tblOne.ColA = tblTwo.ColA
WHERE tblOne.ColC = "Foo" AND tblTwo.ColC = "Null";
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200611/1 
