February 5, 2005 at 3:43 pm
I'm a newbie. I have a large table that holds descriptions for many types of codes used in tables:
table CodeLookUp:
Code int
Description varchar(150)
CType int
Now I am trying to develop a view which interprets all those codes so I can put the descriptive value in a report.
Here is my FROM clause:
FROM
dbo.OccMain ocm
INNER JOIN dbo.OccMalignant omg ON ocm.AilmentID = omg.AilmentID
LEFT JOIN dbo.CodeLookUp c ON c.Code=omg.SentNode WHERE c.CType=65
Am I on the right track? Will I need another 20 LEFT Joins for each code in the tables? And how do I designate the desired code in the SELECT statement? How do you guys handle this? Should I break my CodeLookup table into 100 or more tiny individual tables, or make 100 views with each single code?
Thanks!! 🙂
--
sam
February 7, 2005 at 3:13 am
You just need the following:
FROM
dbo.OccMain ocm
INNER JOIN dbo.OccMalignant omg ON ocm.AilmentID = omg.AilmentID
LEFT JOIN dbo.CodeLookUp c ON c.Code=omg.SentNode
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 7, 2005 at 7:42 am
You can also do it this way...
FROM
dbo.OccMain ocm
INNER JOIN dbo.OccMalignant omg ON( ocm.AilmentID = omg.AilmentID)
LEFT JOIN dbo.CodeLookUp c ON( c.Code=omg.SentNode AND c.CType = 65)
I wasn't born stupid - I had to study.
February 7, 2005 at 1:59 pm
I don't know how or if these guys answered your question, but....
"Am I on the right track? Will I need another 20 LEFT Joins for each code in the tables? "
If you have codes associated with 20 columns, then yes, you'll need 20 left joins. If your code table was broken out into 20 tables, then you'd still need 20 left joins to 20 different tables. But since all your codes are in one table you'll be doing 20 joins to one table.
This is a horrible design for a number of reasons:
Performance. each join searches whole table (alleviated some by composite PK on "Code" AND "CType").
Integrity. How do you verify that the correct "Code" ids are put in the correct column? The only way is using Triggers, and this is bad. If the codes for each column are in a different table, then you can use FK constraints, which is the correct way to do it.
For more on this discussion go to:
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=132&messageid=130464
Signature is NULL
February 7, 2005 at 3:04 pm
Calvin: yes, thanks for this article. I posted this same question on the MSDN newsgroup and got thoroughly "burned" for the same reason. So I plan on making some changes.
But I have to say that what really amazes me is the vast spectrum of ignorance or lack of "best practices" in the programming world. This is not my field and I naively assumed that I could flag down the first developer I came across and they would set me straight on database design as well as middle and front tier stuff. Boy was I wrong. Now I feel foolish for assuming that would be the case.
But what this all says to me is that there is a real business opportunity for someone willing to hang in there and persist until it's really done right. I can tell you from experience in other fields, that "really right" is much less expensive in the LONG run (not the short run).
Thanks for the input.
Sam
February 7, 2005 at 3:23 pm
No problem, glad you help. There are definitely some simple "best practices" to follow, but you are right that there is lot of ignorance regarding these. And people still argue against these practices, or actually recommend breaking them (as you saw from the article). Craziness.
People seem to figure they should BREAK the rules unless there is a good reason not to; while I say you should FOLLOW the rules unless there is a good reason not to. And unfortunately a lot of databases are designed by non-database programmers. It's not like building databases is that hard, but you do need to know what you are doing.
You are totally right that a little extra work up front will mean much less extra work later. It's much much more difficult to re-design a bad database then to build it correctly from the beginning.
Good luck, and post again if you have problems. And remember to index those FK columns in your main table.
Signature is NULL
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply