IIf in Inner Join?

[ormsk]

I am not sure if this is possible...

I want to create a query that will dynamically refer to an INNER JOIN statement dependant on a value of a field

e.g

SELECT Documents.ID , Documents.Type
FROM Documents INNER JOIN Owners ON Documents.UserID = Owners.ID;

The above is straightforward enough, but the [red] Documents.Type [/red] has values where there is no lookup table. For example, if [red] Documents.Type =9 [/red], then I want the dat returned to be as though the SQL statement is ...

SELECT Documents.ID , Documents.Type
FROM Documents INNER JOIN DifferentTable ON Documents.UserID = DifferentTable.ID;

I am not the gatekeeper of the database, so I am unable to add or modify tables, but I want to be able to get aroung this problem.


**********************************
There is more than one way to skin a cat...but who wants a skinned cat?

 

[Remou]

Would a union suit?

SELECT Documents.ID , Documents.Type
FROM Documents INNER JOIN Owners ON Documents.UserID = Owners.ID 
WHERE Documents.Type<>9
UNION
SELECT Documents.ID , Documents.Type
FROM Documents INNER JOIN DifferentTable ON Documents.UserID = DifferentTable.ID
WHERE Documents.Type=9

Depending on your set-up, a union of Owners and Different table may also suit.