This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
I have a bunch of tables that have primary keys. THe main table tblA has a primary key event_id and a varchar field event_type. Depending on the event_type I want to join to a specific table. create table tblA.
For example, if the event_type is "B", I want to join the main table tblA with tblB using the event_id (primary_key, foreign key relation) and get info from tblB.
If event_type is "C", I only join the main table tblA to tblC, to get info from tblC.
I want to do all this in one call using a good join query or a stored procedure. My parameter to the sp/query should be just customer_id.
IMHO this is poorly modelled. Your two tables (tblB & tblC) are identical. So what do you hope to gain by having two tables instead of one? I think your problem with needing a query which uses conditional joins comes from this - so either redesign your data model, or perhaps explain why you need it modelled in this way.
It may be that your example here is oversimplified and in fact your real-life B and C are different. In that case there are several possibilities:
* if the overall width of the rows in B and C isn't very large, you could just put all the columns into A, and simply use NULL for irrelevant columns on any particular row. Then you have just one table and the querying is easy.
* if you do use the three tables as you describe, you can use a query with two left outer joins (from A to B and from A to C). This isn't as efficient as you might like though.
* Some relational databases have "object/relational" features which might solve your problem but in a very non-portable way.
* you might consider using an object/relational mapping tool. What you really have on the Java side is a little inheritance hierarchy, and the O/R mappers will work out the appropriate queries for you. (This choice only makes sense if you move all or almost all of you app over to use the tool.)