Discussion :

[jfouche]

Bonjour

Je suis avec sqlite3, et je souhaite faire une appli de gestion de projet. Pour info, je suis assez nul en SQL, et je ne suis pas sûr de maitriser les jointures.

J'ai créé les tables suivantes :

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
create table if not exists projects(
id integer primary key autoincrement, 
name text)
 
create table if not exists costs(
id integer primary key autoincrement,
unit_cost real, 
quantity real, 
name text, 
id_project integer not null)
 
create table if not exists work_requests(
id integer primary key autoincrement, 
name text, 
cost real, 
id_project integer not null)

la seconde table correspond aux dépenses, et la troisieme aux pepetes qu'on me donne pour faire le projet.
Afin d'avoir un bilan, je me suis fait les vues suivantes :

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
create view if not exists view_board as 
select 
  p.id as prj_id, 
  p.name as prj_name, 
  c.id as cost_id, 
  c.unit_cost as unit_cost, 
  c.quantity as cost_quantity, 
  c.unit_cost * c.quantity as cost, 
  c.name as cost_name 
from costs c, projects p, work_requests wr 
where p.id = c.id_project
 
create view if not exists view_sumary as 
select 
  wr.id_project as prj_id, 
  wr.cost as work_request, 
  sum(view_b.cost) as cost, 
  wr.cost - sum(view_b.cost) as remains 
from view_board view_b 
left join work_requests wr 
on view_b.prj_id = wr.id_project

Ou la premiere vue correspond à la liste des dépenses, et la seconde, un bilan du budget que j'ai, ce que j'ai dépensé, et ce qu'il me reste.

Si je n'ai pas encore de dépenses (ou pas encore de budget), la vue view_sumary ne possede aucun enregistrement.
Y a t il un moyen pour que cette vue quelquechose comme

Code :

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

id | 0.0 | 0.0 | 0.0

si il n'y a pas d'enregistrements ?

[al1_24]

Commence par réécrire la vue view_board avec des jointures normalisées (INNER JOIN)
Tu constateras alors qu'il y a un produit cartésien.
Ensuite, il y a sans doute aussi une jointure externe à mettre en oeuvre dans cette même vue pour qu'elle retourne des lignes en l'absence de dépense (ou de budget)

[jfouche]

Bon, je suis navré, mais je n'ai pas tout compris ce que viens de me dire, mais en relisant le tutorial (SQL de AàZ) sur les jointures, je propose ceci, beaucoup plus simple :

Code :

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

1
2
3
4
5
6
7
8
9
10
create view if not exists view_sumary as 
select 
	p.id                       as project_id,
	sum(wr.cost)               as work_request,
	c.unit_cost * c.quantity   as cost
from projects p
	left join work_requests wr 
	on p.id = wr.id_project
		left join costs c
		on p.id = c.id_project

J'ai enfin des valeurs a des endroits ou je n'en avais pas précédement !
En ce qui concerne le calcul de la différence dans le select (work_request - cost), si vous avez des idées, je suis prenneur. En effet, comme je peux avoir une valeur null si ma table cost est vide, je peux me retrouver avec une valeur null pour la différence. Ceci n'est pas bon. Je prefererai avoir work_request.

[edit] : je ferais mieux de tester avant de poster, ca marche pas

[jfouche]

Bon, je reprends, moins crispé :

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
create table if not exists projects (
	id integer primary key autoincrement, 
	name text
)
 
create table if not exists costs (
	id integer primary key autoincrement,
	unit_cost real, 
	quantity real, 
	name text, 
	id_project integer not null
)
 
create table if not exists work_requests (
	id integer primary key autoincrement, 
	name text, 
	cost real, 
	id_project integer not null
)

Je n'arrive même pas à avoir un truc du genre :

Code :

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

id_project | sum(work_request.cost) pour chaque projet

[edit]bon, j'avance : group by est mon ami

[edit2]J'arrive à ça :

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
select 
	p.id     as project_id,
	wr.cost  as work_request,
	c.cost   as cost
from projects p
	left join (
		select 
			id_project,
			sum(cost) as cost
		from work_requests
		group by id_project
		) wr
	on p.id = wr.id_project
		left join (
			select 
				id_project,
				sum(unit_cost * quantity) as cost
			from costs
			group by id_project
			) c
		on p.id = c.id_project

Ca se simplifie ?
Et en remettant 10 balles dans le nourin, on peut avoir la différence entre work_request et cost, même si on n'a pas encore de valeur ?

[CinePhil]

Tu as bien progressé.
Regarde maintenant du côté de COALESCE pour résoudre ton problème de valeur absente dans la différence...