Based on a field

  • Thread starter Thread starter Dimitris Mexis
  • Start date Start date
D

Dimitris Mexis

Let's say, select MoveSign From Money,
when MoveSign=1 I want to join it with Client table
but when MoveSign=2 I want to alter the join to Supplier
and this be done when the query is on the loose
?
Any suggestion?
 
Dimitris said:
Let's say, select MoveSign From Money,
when MoveSign=1 I want to join it with Client table
but when MoveSign=2 I want to alter the join to Supplier
Click to expand...

Jet does not support e.g.

AND [Money].MoveSign = 1

within the JOIN syntax so you have to put it in the WHERE clause e.g.

SELECT [Money].ID, [Money].MoveSign
FROM [Money], Client
WHERE [Money].ID = Client.ID
AND [Money].MoveSign = 1
UNION
SELECT [Money].ID, [Money].MoveSign
FROM [Money], Supplier
WHERE [Money].ID = Supplier.ID
AND [Money].MoveSign = 2

This is OK for an INNER JOIN but you may get unexpected results with an
OUTER JOIN so do extensive testing. For info, do a google search for,
"There are limitations and very serious problems with the extended
equality version of an outer join used in some diseased mutant
products" (yes, Access/Jet is one of the aforementioned mutants <g>).
 
Dimitris said:
Let's say, select MoveSign From Money,
when MoveSign=1 I want to join it with Client table
but when MoveSign=2 I want to alter the join to Supplier
Click to expand...


Jet does not support e.g.

AND [Money].MoveSign = 1

within the JOIN syntax so you have to put it in the WHERE clause e.g.

SELECT [Money].ID, [Money].MoveSign
FROM [Money], Client
WHERE [Money].ID = Client.ID
AND [Money].MoveSign = 1
UNION
SELECT [Money].ID, [Money].MoveSign
FROM [Money], Supplier
WHERE [Money].ID = Supplier.ID
AND [Money].MoveSign = 2

This is OK for an INNER JOIN but you may get unexpected results with an
OUTER JOIN so do extensive testing. For info, do a google search for,
"There are limitations and very serious problems with the extended
equality version of an outer join used in some diseased mutant
products" (yes, Access/Jet is one of the aforementioned mutants <g>).
Click to expand...
thank you I ll do and the googling too...
 
Back
Top