Fetching dependent records from the same table
-
Hello, I am new to databases. I need urgent help. I have a table named Category, which contains 4 fields - categoryid, siteproprtyid, topCategoryId. the topcategoryid contains the categoryid of the higher level category. Likewise there are four level of category. An example to this is- categoryid sitepropertyid topcategoryid ---------- -------------- ------------- 1 16 Null 2 16 1 3 16 2 4 16 3 5 16 1 I want the output to be like Category1 Category2 Category3 Category4 --------- --------- --------- --------- 1 2 3 4 1 5 Please help me regarding this
-
Hello, I am new to databases. I need urgent help. I have a table named Category, which contains 4 fields - categoryid, siteproprtyid, topCategoryId. the topcategoryid contains the categoryid of the higher level category. Likewise there are four level of category. An example to this is- categoryid sitepropertyid topcategoryid ---------- -------------- ------------- 1 16 Null 2 16 1 3 16 2 4 16 3 5 16 1 I want the output to be like Category1 Category2 Category3 Category4 --------- --------- --------- --------- 1 2 3 4 1 5 Please help me regarding this
Hi, your question is not so clear. As of now what I understood, you need a row column transposition which can be achieved using PIVOT function of SQL SERVER . You are saying that there are 4 level of categories. In the output, that you presented here, I cannot find any sort of link between the first and second rows. What is 5 doing in the second row? How is it related in this context? Does 1 will always appear in the Category1? Is this 1, the TOPCATEGORYID and is it a demarcation indicating for a next row? Category1 Category2 Category3 Category4 --------- --------- --------- --------- 1 2 3 4 1 5 Explain everything clearly... then it will be easier to answer your problem. :)
Niladri Biswas
modified on Friday, June 26, 2009 10:14 PM
-
Hi, your question is not so clear. As of now what I understood, you need a row column transposition which can be achieved using PIVOT function of SQL SERVER . You are saying that there are 4 level of categories. In the output, that you presented here, I cannot find any sort of link between the first and second rows. What is 5 doing in the second row? How is it related in this context? Does 1 will always appear in the Category1? Is this 1, the TOPCATEGORYID and is it a demarcation indicating for a next row? Category1 Category2 Category3 Category4 --------- --------- --------- --------- 1 2 3 4 1 5 Explain everything clearly... then it will be easier to answer your problem. :)
Niladri Biswas
modified on Friday, June 26, 2009 10:14 PM
Hi, yea may be I was not able to put my question properly. Let me try once more. Suppose CategoryId 1 is the root level, so thete is no top level category for it and it is null. The CategoryId 2 is the child of CategoryId 1, therefore, the topcategoryid for Category 2 is 1. Like wise for Category 3, the topcategoryid is 2 and for CategoryId 4, the topcategoryid is 3. CategoryId 5 is the child of CategoryId 1 and there are further no child categories under it. Therefore, I showed thebase table like that. For the same, I want the output in the manner I depicted. I hope I am able to explain this time. Thanks for bearing with me.
-----Have A Nice Day-----
-
Hello, I am new to databases. I need urgent help. I have a table named Category, which contains 4 fields - categoryid, siteproprtyid, topCategoryId. the topcategoryid contains the categoryid of the higher level category. Likewise there are four level of category. An example to this is- categoryid sitepropertyid topcategoryid ---------- -------------- ------------- 1 16 Null 2 16 1 3 16 2 4 16 3 5 16 1 I want the output to be like Category1 Category2 Category3 Category4 --------- --------- --------- --------- 1 2 3 4 1 5 Please help me regarding this
If you are using SQL Server 2008 then look into the hierarchy ID. You actually have 2 problems, getting a reasonable hierarchy and then pivoting the result set. This may be of some use[^].
Never underestimate the power of human stupidity RAH
-
Hello, I am new to databases. I need urgent help. I have a table named Category, which contains 4 fields - categoryid, siteproprtyid, topCategoryId. the topcategoryid contains the categoryid of the higher level category. Likewise there are four level of category. An example to this is- categoryid sitepropertyid topcategoryid ---------- -------------- ------------- 1 16 Null 2 16 1 3 16 2 4 16 3 5 16 1 I want the output to be like Category1 Category2 Category3 Category4 --------- --------- --------- --------- 1 2 3 4 1 5 Please help me regarding this
Sorry for being late in answering your question. I was completely bogged up with my project. However, please try this
SELECT F.CATEGORY1,F.CATEGORY2,F.CATEGORY3,F.CATEGORY4
FROM(
SELECT C1.TOPCATEGORYID AS CATEGORY1,
C1.CATEGORYID AS CATEGORY2,
C2.CATEGORYID AS CATEGORY3,
C3.CATEGORYID AS CATEGORY4FROM CATEGORY AS C1
LEFT OUTER JOIN CATEGORY AS C2
ON C2.TOPCATEGORYID = C1.CATEGORYID
LEFT OUTER JOIN CATEGORY AS C3
ON C3.TOPCATEGORYID = C2.CATEGORYID
WHERE C1.TOPCATEGORYID IS NOT NULL) FWHERE F.CATEGORY1 = 1
N.B.~ The inner derived query is the right one. It is showing the right dependencies. Since I had to bring precisely to your format, so I applied the outer query. However, it will work as per the input you gave and as per your desired output Let me know in case of any concerns. :)
Niladri Biswas
-
Sorry for being late in answering your question. I was completely bogged up with my project. However, please try this
SELECT F.CATEGORY1,F.CATEGORY2,F.CATEGORY3,F.CATEGORY4
FROM(
SELECT C1.TOPCATEGORYID AS CATEGORY1,
C1.CATEGORYID AS CATEGORY2,
C2.CATEGORYID AS CATEGORY3,
C3.CATEGORYID AS CATEGORY4FROM CATEGORY AS C1
LEFT OUTER JOIN CATEGORY AS C2
ON C2.TOPCATEGORYID = C1.CATEGORYID
LEFT OUTER JOIN CATEGORY AS C3
ON C3.TOPCATEGORYID = C2.CATEGORYID
WHERE C1.TOPCATEGORYID IS NOT NULL) FWHERE F.CATEGORY1 = 1
N.B.~ The inner derived query is the right one. It is showing the right dependencies. Since I had to bring precisely to your format, so I applied the outer query. However, it will work as per the input you gave and as per your desired output Let me know in case of any concerns. :)
Niladri Biswas
-
Hi, I did not run the query, but as a dry run, it looks to me absolutely perfect. On Monday, after reaching office, I will try it and will send you the response. Can you kindly send me ur email id. Thanks
-----Have A Nice Day-----
Dear Neeraj, I have tested that query and with varying inputs. It worked fine in all the situations. The inner query is doing the necessary job. It is bringing out all the dependent records. I wrote the outer query only to satisfy your requirement. But anyways, I enjoyed to solve this kind of queries. By the way, you can always mail me by clicking on the email link provided here in the message section :)
Niladri Biswas
modified on Saturday, June 27, 2009 11:16 PM