March 28, 2004 at 4:05 am
i have a table called codeMaster which has the Following structuer and some data
CodeMaster Table
-------------
Code Desc
----- ------
001 England
002 USA
003 Russia
004 department1
005 Departmen2
. . . . .
I have another table called details which has the following fields that
contain the code from the above table
Details Table
-----------
Name Country Department
------ --------- -------------
John 002 004
Bill 001 005
. . . . . . . . . . .. etc.
I want to write a query which will return me a query like below
Name Country Department
------ --------- --------------
John USA Department1
do I write the query to get the above result. The Details table will contain more than 20
fields. Also Codemaster table will have more than 1000 codes. I dont know how to write such
a query in the most efficient manner. any help would be greately appreciated. thanks!
March 28, 2004 at 9:55 am
Your will have to use multiple joins. For you example it will be
SELECT
Det.[Name],
CM1.Desc AS Country,
CM2.Desc AS Department
FROM
dbo.Details Det
Inner Join
dbo.Codemaster CM1
ON
Det.Country = CM1.Code
Inner Join
dbo.Codemaster CM2
ON
Det.Department = CM2.Code
March 28, 2004 at 7:14 pm
HI,
I could have done with inner joins,but my main concern is if I have say 50 fields, would I require to have 50 inner joins for each field in details table!!!. if so how efficient is it.. As u have shown u have already INNER JOINed Details with two Codemaster references CM1, CM2 because I have given only two fields in Details table.. what if the Details table had 100 fields... this is my core concern.. thanks.
rgds
pradep
March 28, 2004 at 7:55 pm
You might could try with 1 join and do and OR to get each columns match (but this will create multiple rows). From there on each column you use a CASE if match with number then Desc else NULL and rollup with a group by.
Something like
SELECT
Det.[Name],
MAX(CASE WHEN Det.Country = CM.Code THEN CM.Desc ELSE NULL END) AS Country,
MAX(CASE WHEN Det.Department = CM.Code THEN CM.Desc ELSE NULL END) AS Department
FROM
dbo.Details Det
Inner Join
dbo.Codemaster CM
ON
Det.Country = CM.Code OR
Det.Department = CM.Code
GROUP BY
Det.[Name]
Of course a large number of rows may cause a slow down and a large number of columns could too. And you could incorrect results if Det.[Name] is not unique.
I might be inclined to just use the joins instead or reconsider the design.
March 29, 2004 at 4:07 pm
Could you add a codetype table so that you can join to it to make the cross tab easier? If you logically group the codes in CodeMaster into a codetype then add the code type id field to the codemaster table you should be able to group the data easier and get your cross table working easier. Then each field name in your cross tab report would be a record value from your codetype table (Country, Department, etc).
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
March 29, 2004 at 9:00 pm
Hi gary,
thanks a lot for that suggestion. I have done what u said. created a codetypemaster table. here is what I have comeout with
select * from codetypemaster CTM join codemaster CDM on ctm.nmCodeGroup = CDM.nmCodeGroup
where CDM.nmCode in (2,4)
2 and 4 are codes from codemaster where 2 is one of the codes from a group of codes for country and 4 is one of the codes from a group of department..
any more tips on optimization on the above or any other way that i can do the same.
thanks a lot.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply