Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Dynamic table selects

 
Nagya Chindhi
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Loren Rosen
Ranch Hand
Posts: 156
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic