Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ehab_developer
Starting Member
1 Post |
 Posted - 2011-05-14 : 08:47:09
|
| Hi every onei need a select query from three tables to show item and sub item (level on) and sub sub item(level two) ,my problem is when i select with using join i get more than item1 in the result of the query in case the item1 have more than one sub , so i need the query to complete this menu and this us the three tables. CREATE TABLE [dbo].[Category] ( [CategoryID] [int] PRIMARY KEY IDENTITY , [CategoryName] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Remarks] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GO CREATE TABLE [dbo].[CategorySub] ( [SubCategoryID] [int] NOT NULL , [CategoryIDToSub] [int] NULL , [SubCategoryName] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [RcatID] [int] NULL , [RsubID] [int] NULL , [Rsub2ID] [int] NULL , [Remarks] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GO CREATE TABLE [dbo].[CategorySub2] ( [Sub2CategoryID] [int] NOT NULL , [SubCategoryID] [int] NULL , [Sub2CategoryName] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [RcatID] [int] NULL , [RsubID] [int] NULL , [Rsub2ID] [int] NULL , [Remarks] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GO plzzzzzzzzz heeeeeeeeeelp |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-15 : 06:28:19
|
This is to be expected . If a given category has more than one sub-category, you will get as many rows as there are in the sub-category when you do a join.If you want only one row from category table, but still join with the sub-category table, you have to decided which one of the many rows in the sub-category table you want to include. That can be done in a few different ways, for example, pick one of the rows from sub-category table at random (see example below), or order the subcategory table based on some criteria and pick the top 1 etc.;with cte as( select c.categoryId, c.categoryName, cs.SubCategoryID, cs.SubCategoryName, cs2.Sub2CategoryID, cs2.Sub2CategoryName, row_number() over (partition by categoryid order by (select null)) as rownum from category c inner join categorysub cs on cs.CategoryIDToSub = c.CategoryId inner join categorysub2 cs2 on cs2.SubCategoryId = cs.SubCategoryId)select * from cte where rownum = 1; |
 |
|
|
|
|
|
|
|
select from three table to dynamic menu control