join typecast

[menelik]

I have 3 tables X, Y, and Z. X and Y are linked by fields of type Text
(A and B respectively). Y is related to Z by a field of type Numeric
(C).

I need to JOIN these fields to relate X to Z. Is it possible to
typecast either column C or columns A and B in a JOIN statement (using
CAST or CONVERT)? Are nested Joins even a good idea?

I know this is poor design but it's a situation that I walked into as
is. It would be nice if I could avoid modifying any table designs...

 

[Michel Walsh]

Hi,


In theory the ON clause should be able to support expressions that return a
Boolean.

With Jet, if C is an integer, there should be an automatic cast, or if that
fails on you, try

SELECT ...
FROM (x INNER JOIN y ON x.a=y.b)
INNER JOIN z ON x.a=CStr(z.c)


Using an explicit cast (or CONVERT in MS SQL Server) forbid the use of the
index, if there is one. But as you said, you didn't make it, just inherit of
this design.

A final test you can make, if the inner join fails, is to use the WHERE
clause:


SELECT ...
FROM (x INNER JOIN y ON x.a=y.b) , z
WHERE x.a = CStr(z.c)



Hoping it may help,
Vanderghast, Access MVP

 

[menelik]

Both worked fine. Thank you very much sir.

 

[menelik]

Or madame...

 

[Michel Walsh]

Hi,

Would be with an ending e if it was so.


Vanderghast, Access MVP