November 26, 2007 at 3:36 am
Hi all,
I have 2 tables Document and Department. Document table will contain Doc_id,Doc_name columns. Department contains Dept_id, Dept_Desc and document_list (which stores Doc_id in a comma seperated manner) columns.
sample data in my tables will be like this (it may help you to get into the problem)
Document table
Doc_id Doc_name
1 aaa
2 bbb
3 ccc
Department table
Dept_id Dept_Descdocument_list
1 xxx1,2
2 yyy2,3
3 zzz1,2,3
now i want to display all departments for a given document id (for example if document id is 3 then departments will be yyy and zzz).
can someone help me to find the solution?
thanks in advance.
November 26, 2007 at 3:52 am
Hi Raghavendra
Your second table is not normalised. The comms seperated document list is the problem.
You should remove the document list from the department table and create a third table to hold department id and a single document id like this.
dept_id doc_id
1 1
1 2
2 2
2 3
3 1
3 2
3 3
You then have a simple join to say which depts have which docs.
Allen
November 26, 2007 at 4:24 am
Hi Allen,
thanks for your suggestion, now i did this using like operator, will it affect the performance of sql server if i use like operator?
November 26, 2007 at 4:30 am
just try out this code
Select Detp_Desc
from document a ,department b
where a.doc_id = right(document_list,1)
karthik
November 26, 2007 at 4:31 am
Hi Raghavendra
Not sure I understand how you did it using LIKE.
Regarding performance it really depends on your SQL query and the indexes you have on your table.
Allen
November 26, 2007 at 4:49 am
Hi, All
I have created a stored procedure like this.
create procedure sp_getRelatedDepartments
(
@documentid int
)
as
SELECT * FROM Department WHERE document_list=@documentid OR document_list like '%,'+@documentid OR document_list like '%,'+@documentid+',%' OR document_list like @documentid+',%'
November 26, 2007 at 4:57 am
But will your stored proc differentiate between 1 and 11 ?
November 26, 2007 at 5:01 am
yes , it will differentiate 1 and 11
November 26, 2007 at 5:06 am
Ragavendra,
how are you saying it will differentiate ?;) I think it should not.
Going a little detail , basically you should avoid using 'OR' strategy in your queries. It would produce 'Cartesian Product'.
karthik
November 26, 2007 at 5:06 am
Unfortunately, using the LIKE operator always affects query performance. For one, if you use the wildcard on both sides of the search syntax, then you can inadvertantly generate tablescans.
The bigger the table, the bigger the performance hit. You may not notice it at first, but as your database grows, using the LIKE operator can cause future significant problems. So it's best to redesign your tables now as previously suggested (if you can) to avoid the problem.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply