aspose file tools*
The moose likes JDBC and the fly likes Dynamic table selects Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Dynamic table selects" Watch "Dynamic table selects" New topic
Author

Dynamic table selects

Nagya Chindhi
Greenhorn

Joined: Jun 30, 2004
Posts: 3
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.

Can someone help?

Thanks,
Nagya


CREATE TABLE [tblA] (
[event_id] [int] ,
[event_type] [nvarchar] (50),
[customer_id] [int] ,
CONSTRAINT [PK_tblA] PRIMARY KEY CLUSTERED
(
[event_id]
) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [tblB] (
[event_id] [int],
[somefieldB1] [nvarchar] (50),
[somefieldB2] [nvarchar] (50),
CONSTRAINT [PK_tblB] PRIMARY KEY CLUSTERED
(
[event_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [tblC] (
[event_id] [int] NOT NULL ,
[somefieldC1] [nvarchar] (50) ,
[somefieldC2] [nvarchar] (50) ,
CONSTRAINT [PK_tblC] PRIMARY KEY CLUSTERED
(
[event_id]
) ON [PRIMARY]
) ON [PRIMARY]
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Loren Rosen
Ranch Hand

Joined: Feb 12, 2003
Posts: 156
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.)
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Dynamic table selects