inheritance and dynamic joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

inheritance and dynamic joins

Author

Topic

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-02-23 : 22:14:29

howdy gurus - want some advice....

have a schema where some clever person has decided to model object inheritance using "dynamic joins" (the clever person being some multi-national organisation). I need some good suggestions about how to go about how to create views etc...Any suggestions gladly accepted.

eg
table "object" has a column "category"
depending on the value of category (either "inf" or "hw")

I would either
join object with information_details or
join object with hardware details
to get the appropriate report.

eg
create table obj (obj_id int, cat nvarchar(10), obj_value money)
create table inf_det (inf_obj_id, details nvarchar(200))
create table hw_det (hw_obj_id, details nvarchar(200))
insert into obj (1,'hw', 2000.00)
insert into obj (2,'inf', 0.00)
insert into hw_det(1,'hardware is for losers')
insert into inf_det(2,'too much information is not enough')

--hardware
select obj.obj_value, hw.details
from obj,hw_det
where obj.obj_id = hw.hw_obj_det
and obj.cat = 'hw'

--info
select obj.obj_value, inf.details
from obj,inf_det
where obj.obj_id = inf.inf_obj_det
and obj.cat = 'inf'

why-oh why did they do it??

Obviously I can't use a case statement in a join. AARRRGHHH! Please some advice for a poor, lonely little developer....

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-02-23 : 23:14:15

quote:
Obviously I can't use a case statement in a join.

Why would you think that?

create table obj (obj_id int, cat nvarchar(10), obj_value money)
create table inf_det (inf_obj_id int, details nvarchar(200))
create table hw_det (hw_obj_id int, details nvarchar(200))
insert into obj values (1,'hw', 2000.00)
insert into obj values (2,'inf', 0.00)
insert into hw_det values (1,'hardware is for losers')
insert into inf_det values (2,'too much information is not enough')

select obj.obj_value,
CASE obj.cat
WHEN 'hw' THEN hw.details
WHEN 'inf' THEN inf.details END AS Details
FROM obj INNER JOIN hw_det hw ON obj.obj_id =
CASE obj.cat WHEN 'hw' THEN hw_obj_id ELSE obj.obj_id END
INNER JOIN inf_det inf ON obj.obj_id =
CASE obj.cat WHEN 'inf' THEN inf_obj_id ELSE obj.obj_id END
AND obj.cat IN('hw','inf')

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-02-23 : 23:21:34

Sounds like horizontal partioning to me.

What is it you would like the views to do?
Are the id's reused in both the hardware and the info table or are they different?
If they are equal do they belong to the same record?

You could create a view that unions both hardware details and information details

CREATE VIEW "Inf_Hw" AS
SELECT inf_obj_id As obj_id,details
from inf_det
UNION ALL
SELECT hw_obj_id As obj_id, details
FROM hw_det

then create a stored procedure that takes category as a parameter.

CREATE PROCEDURE GetObjectsByCategory
@Category nvarchar(10)
AS
SELECT
obj.obj_value, Inf_Hw.details
FROM
obj INNER JOIN Inf_Hw ON obj.obj_id = Inf_Hw.obj_id
WHERE
obj.cat = @Category

CREATE PROCEDURE GetAllObjects
@Category nvarchar(10)
AS
SELECT
obj.obj_value, obj.cat, Inf_Hw.details
FROM
obj INNER JOIN Inf_Hw ON obj.obj_id = Inf_Hw.obj_id

If you feel there is no reason for the partitioning you could combine all the information into 1 table.

Edited by - ValterBorges on 02/23/2003 23:27:01

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-23 : 23:24:38

Isn't this what LEFT OUTER JOINs are for ???

SELECT obj.*,
CASE WHEN cat = 'hw' THEN hw_det.details ELSE inf_det.details END as Details
FROM
obj
LEFT OUTER JOIN hw_det ON
obj.obj_id = hw_det.hw_obj_id
LEFT OUTER JOIN inf_det ON
obj.obj_id = inf_det.inf_obj_id

- Jeff

Edited by - jsmith8858 on 02/23/2003 23:25:45

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-02-23 : 23:41:43

quote:

Isn't this what LEFT OUTER JOINs are for ???

Well, that at least answers my next question!!

Thanks laddies....

PS Valter - not my tables - just my problem

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

SamC
White Water Yakist

3467 Posts

Posted - 2003-02-23 : 23:46:07

Another method....

select obj.obj_value,
CASE
WHEN OBJ.CAT='hw'
THEN (SELECT hw.details from hw_det WHERE hw_obj_det=obj.obj_id)
ELSE (select inf.details from inf_det where inf_obj_det=obj_id)
END AS Details
FROM obj

----------------------------------

Here's a big if: If hw_obj_det and inf_obj_det do not have duplicate values, you could union the two before joining eliminating any CASE statements at all.

SELECT obj_value, details
FROM obj
INNER JOIN
(
select hw_obj_det as obj_id, details
from hw_det
UNION ALL
select inf_obj_det as obj_id, details
from inf_det
) X on x.obj_id=obj.obj_id

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2003-02-24 : 01:23:22

And another. No Cases, no IF's or buts.

SELECT obj_value, details
FROM obj
INNER JOIN
(
select 'hw' as cat, hw_obj_det as obj_id, details
from hw_det
UNION ALL
select 'inf' as cat, inf_obj_det as obj_id, details
from inf_det
) X on x.obj_id=obj.obj_id and x.cat = obj.cat

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources