Join on alias field values on-the-fly

Join on alias field values on-the-fly

Post by jason.tee » Fri, 10 Nov 2006 12:48:41


Hi,

Is it possible to join on an alias for a field value on-the-fly.
Something like the nz() function, but not only for Null values. I want
it to be for anything I stipulate as equivalent.

For example:

tblOne
------------------------------------
ColA | colB | colC
------------------------------------
A1 | B1 | Foo
A2 | B2 | Null
A3 | B3 | Bar
A4 | B4 | Foo
A5 | B5 | Foo
A6 | B6 | Foo


tblTwo
------------------------------------
ColA | colB | colC
------------------------------------
A1 | B1 | Null
A2 | B2 | Null
A3 | B3 | Bar
A4 | B4 | Null
A5 | B5 | Null
A6 | B6 | Null


When I join tblOne and tblTwo, I want it to say that if tblOne colC has
value "Foo" its equivalent to "Null" so if I join on that then it will
see it as a match and appear in the SELECT query.

Thanks
 
 
 

Join on alias field values on-the-fly

Post by Granny Spi » Fri, 10 Nov 2006 13:16:38


Hon, you can't join on nulls because null isn't equal to anything, not even
another null.

This is a shot in the dark, but have you tried concatenation with the plus
operator instead of the ampersand in your join clause?

FROM tblOne INNER JOIN tblTwo ON tblOne.ColC + "match" = tblTwo.ColC +
"match"

--
Message posted via http://www.yqcomputer.com/

 
 
 

Join on alias field values on-the-fly

Post by Tom van St » Fri, 10 Nov 2006 13:31:45


How do you know that Foo pairs up with Null? I'm thinking because
both have A1 and B1.
So use that in your join: draw a line from ColA to ColA, and from ColB
to ColB.

-Tom.
 
 
 

Join on alias field values on-the-fly

Post by jason.tee » Fri, 10 Nov 2006 13:34:44

The "Null" in this case is actaully a String with the text literal
"Null"

NOT a vbNull and not an empty field.
 
 
 

Join on alias field values on-the-fly

Post by jason.tee » Fri, 10 Nov 2006 13:37:14

Ohh becuase we can have a scenario where there is other things in ColC
other than just "null" and "foo"

where ColA and ColB match. and I only want it to pick it out if ColC is
"Foo" from tblOne and ColC is "Null" from tblTwo, with ColA and ColB
the same.

Thanks.
 
 
 

Join on alias field values on-the-fly

Post by Granny Spi » Fri, 10 Nov 2006 13:47:12


Ok I'll bite. Why are you storing the string "Null" in your table? Is it
because the column disallows both nulls and zero length strings and your boss
wasn't going for "whatsit," as that would confuse the users?

--
Message posted via http://www.yqcomputer.com/
 
 
 

Join on alias field values on-the-fly

Post by jason.tee » Fri, 10 Nov 2006 15:07:51

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.

Thanks.
 
 
 

Join on alias field values on-the-fly

Post by Granny Spi » Sat, 11 Nov 2006 03:11:59


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.yqcomputer.com/
 
 
 

Join on alias field values on-the-fly

Post by Charle » Wed, 15 Nov 2006 08:01:31

Stab in the dark but can't you say
Select * from tblOne as A, tblTwo as B where A.ColC=B.ColC or
(a.ColC='Foo' and b.ColC='Null')
Or am I missing something??