Case statement

[Naoise]

I have 3 tables

tblActivityResource
ActivityID [int] | ResourceID [int]
1 1
1 2
...

tblResource
ResourceID [int] | ResourceTypeID [varchar]
1 'ActivityOne'
2 'ActivityTwo'

tblResourceText
ResourceID [int] | LanguageCode [varchar] | ResourceText [ntext]
1 'EN' 'Activity 1 name'
2 'EN' 'Activity 1 description'

If I try the following I will obviously get 2 results


select ResourceID, ResourceText
from tblActivityResource a, tblResource b, tblResourceText c
where a.ActivityID = 1
and a.ResourceID = b.ResourceID
and b.ResourceID = c.ResourceID

Results...
ResourceID | ResourceText
1 | 'Activity 1 name'
2 | 'Activity 1 description'

How would I go about getting

ResourceID | ActivityName | Activity description
1 | 'Activity 1 name' | 'Activity 1 description'

Case statements?

 

[AlexCuse]

First of all, I think your tables need a redesign. You also should not use ntext for such a short column (use something like nvarchar(50)).

But, if you are locked in to this design, here is a query that may work for you (it did for your sample data, assumes only two resource ID's per activity, and lower of the two is always activityname)

select distinct a.ActivityID
, b.ResourceText as ActivityName
, c.ResourceText as ActivityDescription
from tblActivityResource a
inner join
(
select z.ActivityID, z.ResourceID, y.ResourceText
from
(
select ActivityID, min(resourceID) as ResourceID
from tblActivityResource
group by ActivityID
) z
inner join
tblResourceText y
on z.ResourceID = y.ResourceID
) b
on a.ActivityID = b.ActivityID
inner join
(
select x.ActivityID, x.ResourceID, w.ResourceText
from
(
select ActivityID, max(resourceID) as ResourceID
from tblActivityResource
group by ActivityID
) x
inner join
tblResourceText w
on x.ResourceID = w.ResourceID
) c
on a.ActivityID = c.ActivityID

But seriously, try to redesign your tables. It should not be this hard to get the information you are after.

I hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[r937]

i think it's a sleek, robust, normalized, simple and effective design

just in case you wanted another opinion



r937.com | rudy.ca

 

[AlexCuse]

I will defer to you Rudy, I must be missing something

Ignorance of certain subjects is a great part of wisdom

 

[r937]

notice i said the design is okay, but refrained from commenting on the fact that, yes, it is this hard to get the information out



complexity of the sql in this particular instance is due not only to the generalization of the data design to accommodate multiple languages, but at the same time requiring a horizontal cross-tab of results

r937.com | rudy.ca

 

[AlexCuse]

Good to hear I (might) not be doing as badly as I think ;-)

Thx for the explaination, I thought I was missing something simple. It seems to me a bit 'hyper-normalized', but I work primarily with marketing databases where it is difficult to normalize past 3NF, so who am I to judge

Naiose - if it is not so hard to get the other information you need from this db, then please disregard my comment on table design. But let me know if that query works. It did on the somewhat limited test data.

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Naoise]

Thanks for the replies. I've changed the model to look like below.


tblActivity
ActivityID [int] | NameID [int] | DescriptionID [int]
1 1 2

tblActivityResource
ActivityID [int] | ResourceID [int]
1 1

tblResource
ResourceID [int] | ResourceTypeID [varchar]
1 'ACTIVITY'
2 'ACTIVITY'

tblResourceText
ResourceID [int] | LanguageCode [varchar] | ResourceText [ntext]
1 'EN' 'Activity 1 name'
2 'EN' 'Activity 1 description'

I'm trying this at the moment. Any tips on improving this sql statement?

SELECT c.ResourceText as 'ActivityName', e.ResourceText as 'ActivityDescription'
FROM tblActivity a
INNER JOIN tblResource b ON a.NameID = b.ResourceID
INNER JOIN tblResourceText c ON b.ResourceID = c.ResourceID AND c.LanguageCode = 'EN'
INNER JOIN tblResource d ON a.DescriptionID = d.ResourceID
INNER JOIN tblResourceText e ON d.ResourceID = e.ResourceID AND e.LanguageCode = 'EN'
AND a.ActivityID = 1

 

[r937]

change the last line to

WHERE a.ActivityID = 1

while i don;t know your requirements, i think tblResource might be superfluous

r937.com | rudy.ca

 

[PHV]

What is the function of tblResource ?
SELECT A.ActivityID, N.ResourceText ActivityName, D.ResourceText ActivityDescription
FROM tblActivity A
INNER JOIN tblResourceText N ON A.NameID = N.ResourceID AND N.LanguageCode = 'EN'
INNER JOIN tblResourceText D ON A.DescriptionID = D.ResourceID AND D.LanguageCode = 'EN'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Naoise]

Thank you for your replies, they have been helpful.