Discussion :

[gldfdp]

Bonjour,

j'ai 2 tables: product, productcategory.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
 _____________              ____________
|  product    |           |productcategory|
  ----------               ---------------
| id (PK)     |      ---->|id (PK)        |<--
|  name       |     |     |name           |   |
|id_category  |----'      |uppercategory  |--'
 --------------            ---------------

Je n'arrive pas a faire une requete qui me renvoie tous les produits d'une categorie ET de ses sous-categories. Par exemple si j'ai une categorie "portable" (id:3) et une autre "bureau" (id:2) qui sont des souscategorie de la categorie "ordinateur" (id:1), avec dans portable le produit "portable1" (id:1) et dans bureau le produit "bureau1" (id:2), je voudrais retrouver tous les produits appartenant a la categorie ordinateur ET ses sous categories, ce qui me donnerait donc:
id | nom
-------------------
1 | portable1
2 | bureau1

Voici le code de creation de mes tables si vous en avez besoin:
productcategory:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
CREATE TABLE [dbo].[productcategory] (
	[id] [smallint] IDENTITY (1, 1) NOT NULL ,
	[name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[uppercategory] [smallint] NULL 
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[productcategory] WITH NOCHECK ADD 
	CONSTRAINT [DF_productcategory_uppercategory] DEFAULT (null) FOR [uppercategory],
	CONSTRAINT [PK_productcategory] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO
 
ALTER TABLE [dbo].[productcategory] ADD 
	CONSTRAINT [FK_productcategory_productcategory] FOREIGN KEY 
	(
		[uppercategory]
	) REFERENCES [dbo].[productcategory] (
		[id]
	)
GO

product

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
CREATE TABLE [dbo].[product] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[description] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[id_category] [smallint] NULL 
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[product] WITH NOCHECK ADD 
	CONSTRAINT [PK_product] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO
 
ALTER TABLE [dbo].[product] ADD 
	CONSTRAINT [FK_product_productcategory] FOREIGN KEY 
	(
		[id_category]
	) REFERENCES [dbo].[productcategory] (
		[id]
	)
GO

Insertions: (en supposant que les identites sont bien a 0)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
Insert into productcategory (name) values ('ordinateur');
Insert into productcategory (name,uppercategory) values ('bureau',1);
Insert into productcategory (name,uppercategory) values ('portable',1);
Insert into product (name,id_category) values ('portable1',3);
Insert into product (name,id_category) values ('bureau1',2);

[/code]

[vmolines]

C'est une structure arborescente qu'il te faut. Ca se fait soit par auto référence soit par représentation intervallaire (mieux).

Pour ça une seule adresse : http://sqlpro.developpez.com/cours/arborescence/

[romano21]

Hello, essaye ca:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
select p.id,p.name
from product p, productcategory pc
where 
     p.id_category = pc.id AND
     pc.id IN (select distinct pc.uppercategory
                  from  productcategory pc2
                  start with pc2 = id1
                  connect by prior pc2.uppercategory = pc2.id)
    AND pc.id = id1

Voilà, bon courage et tiens nous au courant

[gldfdp]

Merci pour ce lien, mais le probleme est que je me trouve dans le premier cas (autoreferencement), que je veux faire cette requete via de l'apsx (avec ms sql server), et que je peux plus toucher a la structure de ma base et de mes tables.
N'y aurait-il donc pas une requete plus ou moins simple pour au moins trouver tous les produits se rattachant a une categorie et ses descendantes?
Je vous remercie

[gldfdp]

Je teste ton code tout de suite romano. Je te remercie

[Médiat]

Je teste ton code tout de suite romano. Je te remercie

CONNECT BY est spécifique ORACLE et ne fonctionne pas avec SQL Server

[gldfdp]

Oui je viens de m'en apercevoir. Il y a t il un moyen de contourner cela?

[Médiat]

Selon la version de SQL Server que tu utilises, il y a la clause WITH RECURSIVE

[SQLpro]

Il est strictement impossible en une seule requête SQL d'obtenir toute la descendance d'une arborescence sans utiliser :
- soit les techniques de requêtes récursives
- soit la modélisation intervallaire.

MS SQL Server 2000 n'intégre pas les techniques de requêtes récursives. Mais la version 2005 si. Lire l'article que j'ai écrit à ce sujet :
http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp

La modélisation intervallaire peut se greffer au modèle en auto référence sans changer la base. Cela se fait par ajout de la table intervallaire et 3 triggers : DELETE, INSERT et UPDATE.

A +

[gldfdp]

Merci je regardes ton article

[gldfdp]

Bon j'ai trouve une solution grace a vous, je passe par la representation intervallaire.
Pour ceux que ca interesse:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
 
/*Creation de la table*/
CREATE TABLE [dbo].[newcategory] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[nc_lb] [int] NULL ,
	[nc_rb] [int] NULL ,
	[name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO
 
/*Procedure d'insertion dans la table*/
CREATE PROCEDURE newcategory_insert 
	@name varchar(50),
	@father_id int
AS  
BEGIN
    declare @father_rb int
    set @father_rb=(select nc_rb from newcategory where id=@father_id) ; 
    Update newcategory set nc_rb=nc_rb+2 where nc_rb>=@father_rb;
    Update newcategory set nc_lb=nc_lb+2 where nc_lb>=@father_rb;
    Insert into newcategory (nc_lb,nc_rb,[name]) values (@father_rb,@father_rb+1,@name);
END
GO
 
/*Procedure d'update de la table*/
 
CREATE PROCEDURE newcategory_update
	@id int,
	@name varchar(50),
	@newfather_id int
AS  
BEGIN
	declare @rb int
	declare @lb int
	declare @flb int
	declare @frb int
 
 
	set @lb=(select nc_lb from newcategory where id=@id)
	set @rb=(select nc_rb from newcategory where id=@id)
 
 
 
	update newcategory set nc_lb=nc_lb-2 where nc_lb>@rb
	update newcategory set nc_rb=nc_rb-2 where nc_rb>@rb
	update newcategory set nc_lb = nc_lb-1 where nc_lb>@lb and nc_lb<@rb
	update newcategory set nc_rb = nc_rb-1 where nc_rb>@lb and nc_rb<@rb
 
	set @frb=(select nc_rb from newcategory where id=@newfather_id)
	set @flb=(select nc_lb from newcategory where id=@newfather_id)
 
	update newcategory set nc_rb= nc_rb+2 where nc_rb>=@frb
	update newcategory set nc_lb= nc_lb+2 where nc_lb>=@frb
	update newcategory set nc_lb=@frb, nc_rb=@frb+1, name=@name where id=@id
 
END
GO
 
/*Procedure de suppression dans la table*/
CREATE PROCEDURE newcategory_delete
	@id int
AS  
BEGIN 
    	Declare @lb int
	declare @rb int 
	declare @idc int;
	set @idc=(select id from newcategory where id=@id)
	if @idc>1
	begin
    		set @rb=(select nc_rb from newcategory where id=@id)
		delete from newcategory where id=@id
    		Update newcategory set nc_rb=nc_rb-2 where nc_rb>@rb
    		Update newcategory set nc_lb=nc_lb-2 where nc_lb>@rb
	end
	DBCC CheckIdent(newcategory,reseed,1)
	DBCC CheckIdent(newcategory,reseed)
END
GO
 
/*Fonction permettant de retrouver la mere d'une categorie*/
CREATE FUNCTION uppernewcategory (@id int)  
RETURNS int AS
BEGIN 
	declare @lb int
	declare @rb int
	declare @upr int
	declare @ret int
	set @lb=(Select nc_lb from newcategory where id=@id)
	set @rb=(Select nc_rb from newcategory where id=@id)
	set @upr=(select min (nc_rb) from newcategory where nc_rb>@rb and nc_lb<@lb)
	set @ret=(select id from newcategory where nc_rb=@upr)
	return @ret
END
 
GO