Conditional SELECT Statement

[net123]

I have a complex SELECT statement in which it SELECTS about 12 different fields FROM 10 different tables (from 2 different databases) and WHERE there are a bunch of WHERE clauses depending on user entry.

There is one field, say EmpID, in which:

IF EmpID = xx123 THEN the SELECT statement shouldn't be so complex b/c it can't go that deep into the tree of relationships.

ELSE SELECT the COMPLEX query...

So let's say I enter a certain REGION to search for my Employees and I pick WEST, then my SELECT statement should EXECUTE both SELECT statements (if there are 2 of them?) and return something like this:

EmpID.....Region.....Market.....F_ID.....Loc
ab323.....WEST.......Sales......123456...Texas
xx123.....WEST.......Sales......unavail..unavail.
cc848.....WEST.......Marketing..393921...California

As you can see, if EmpID is anything but 'xx123' then all fields are approachable, but if it is 'xx123' then some fields aren't thus it should be a small SELECT statement with 1 or 2 JOIN statements.

SELECT t1.EmpID, t2.Region, t3.Market, t4.F_ID, t5.Loc ...
FROM t1 ... INNER JOIN ... t5
WHERE t2.Region like USER INPUT

Your assistance will be greatly appreciated!

 

[lespaul]

First, you don't mention what database you are working with so, I'll assume Access. It sounds like you are trying to build the query dynamically, if so then you'll need to develop a module and build your query in VBA.

If you're using some other database, then I don't know.

You may want to post in the forum for the specific database you are working with.

Leslie

 

[willir]

net123

You may get away with some fancy select statement.

However, I would approach this with code where I build the select statement depending on the input from the user.

For example, if the input is for the "xx123" employee, then build and run one select statement; otherwise build and run the other.

You can also use code to walk through the relationships. For example, use code to run not one, but several select statements to gather the requierd information.

 

[r937]

[tt]select all, the, columns, from, all, tables
from complexjoin
where empid <> xx123
UNION ALL
select only, some, columns, NULL, NULL, NULL
from simplejoin
where empid = xx123[/tt]

rudy

http://r937.com/