• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Dynamic table selects

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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]
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 156
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.)
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic