Project

General

Profile

« Previous | Next » 

Revision 60657

Added by Michele Artini about 3 years ago

author format: surname, name

View differences:

create_affiliations_tables.sql
1 1 
CREATE TABLE persons (
2 2 
	id       varchar(32) PRIMARY KEY,
3 
	fullname varchar(256) NOT NULL
3 
	name     varchar(256) NOT NULL,
4 
	surname  varchar(256) NOT NULL,
5 
	suffix   varchar(256)
4 6 
);
5 7 

  		




  6
  8
  
    
CREATE TABLE groups (


  		




  ......




  18
  20
  
    

  		




  19
  21
  
    
INSERT INTO groups(id, name, type) VALUES ('UNKNOWN', 'UNKNOWN', 'UNKNOWN');


  		




  20
  22
  
    

  		




  21
  
  
    
CREATE VIEW affiliations_view AS	SELECT


  		




  22
  
  
    
	p.id       as pid,


  		




  23
  
  
    
	p.fullname as pname,


  		




  24
  
  
    
	g.id       as gid,


  		




  25
  
  
    
	g.name     as gname,


  		




  26
  
  
    
	g.type     as gtype,


  		




  27
  
  
    
	a.year     as year


  		




  
  23
  
    
CREATE VIEW affiliations_view AS SELECT


  		




  
  24
  
    
	p.id                                                               as pid,


  		




  
  25
  
    
	TRIM(CONCAT(p.surname, ", ", p.name, " ", COALESCE(p.suffix, ''))) as pfullname,


  		




  
  26
  
    
	p.fullname                                                         as pname,  //TODO DA TOGLIERE


  		




  
  27
  
    
	g.id                                                               as gid,


  		




  
  28
  
    
	g.name                                                             as gname,


  		




  
  29
  
    
	g.type                                                             as gtype,


  		




  
  30
  
    
	a.year                                                             as year


  		




  28
  31
  
    
FROM 


  		




  29
  32
  
    
	persons p 


  		




  30
  33
  
    
	LEFT OUTER JOIN affiliations a ON (p.id = a.pid)


  		












Also available in:  Unified diff