SQL and 2 complex join

I'm trying to optimize a SQL query with MySQL 5.5, regarding 2 separates requests. I'd like to have one single request at the end if possible.

I have a DocumentType table (see schema at the end of this question). I'd like to display "3 DocumentInstance on 5 uploaded for Program #4" for instance.

To count the total of document per program, I have this query:

select p.id as programId, count(dt.id) as totalDocPerProgram
from DocumentType dt
join DocumentTypeInProgram dtip on dti开发者_运维百科p.documentType_id=dt.id
join Program p on dtip.program_id=p.id
group by p.id

To count how many docs the user had already uploaded, classified by program, the following query returns the list of userSession and how many docs he did uploaded previously :

select p2.id as programId, wsc2.id as userSession, count(di2.id) as uploadedDocs
from DocumentType dt2
join DocumentInstance di2 on di2.documentType_id=dt2.id
join WebsiteCase wsc2 on di2.websiteCase_id=wsc2.id
left join Program p2 on p2.id=wsc2.program_id
group by wsc2.id

My Question: Is it possible to have one single query that would return for each program, the totalDocPerProgram and how many document have been already uploaded ? And would it be possible to have something like : "Please upload 2 more Documents for Program #3" ?

Thanks for your help guys,

Nicolas

Here is the Database SQL definition script

CREATE TABLE `DocumentType` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `description` varchar(255) DEFAULT NULL,
  `lifetime` int(11) DEFAULT NULL,
  `maxDocumentSize` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;


CREATE TABLE `Program` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;


CREATE TABLE `DataTypeForProgram` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cardinality` int(11) DEFAULT NULL,
  `dataType_id` bigint(20) DEFAULT NULL,
  `program_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FKA6CED6BFFCC6BB96` (`program_id`),
  KEY `FKA6CED6BF17C2EE7E` (`dataType_id`),
  CONSTRAINT `FKA6CED6BF17C2EE7E` FOREIGN KEY (`dataType_id`) REFERENCES `DataType` (`id`),
  CONSTRAINT `FKA6CED6BFFCC6BB96` FOREIGN KEY (`program_id`) REFERENCES `Program` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE `WebsiteCase` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `creationDate` datetime DEFAULT NULL,
  `lastUpdate` datetime DEFAULT NULL,
  `program_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_Program` (`program_id`),
  CONSTRAINT `FK_Program` FOREIGN KEY (`program_id`) REFERENCES `Program` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE `DocumentInstance` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `url` varchar(255) DEFAULT NULL,
  `documentType_id` bigint(20) DEFAULT NULL,
  `websiteCase_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK8BE8C2F05F1A4ADE` (`documentType_id`),
  KEY `FK8BE8C2F05C57B856` (`websiteCase_id`),
  CONSTRAINT `FK8BE8C2F05C57B856` FOREIGN KEY (`websiteCase_id`) REFERENCES `WebsiteCase` (`id`),
  CONSTRAINT `FK8BE8C2F05F1A4ADE` FOREIGN KEY (`documentType_id`) REFERENCES `DocumentType` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE `DocumentTypeInProgram` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cardinality` int(11) DEFAULT NULL,
  `documentType_id` bigint(20) DEFAULT NULL,
  `program_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK190E2A0A5F1A4ADE` (`documentType_id`),
  KEY `FK190E2A0AFCC6BB96` (`program_id`),
  CONSTRAINT `FK190E2A0AFCC6BB96` FOREIGN KEY (`program_id`) REFERENCES `Program` (`id`),
  CONSTRAINT `FK190E2A0A5F1A4ADE` FOREIGN KEY (`documentType_id`) REFERENCES `DocumentType` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 0;

You can do this, with a query that joins your two existing queries. Like so:

select a.programId, 
       a.totalDocPerProgram, 
       b.upLoadedDocs, 
       (a.totalDocPerProgram - b.upLoadedDocs) as remainingDocs, 
       b.userSession
from (
    select p.id as programId, 
           count(dt.id) as totalDocPerProgram
    from DocumentType dt
    join DocumentTypeInProgram dtip on dtip.documentType_id=dt.id
    join Program p on dtip.program_id=p.id
    group by p.id) a
join (
    select p2.id as programId, 
           wsc2.id as userSession, 
           count(di2.id) as uploadedDocs
    from DocumentType dt2
    join DocumentInstance di2 on di2.documentType_id=dt2.id
    join WebsiteCase wsc2 on di2.websiteCase_id=wsc2.id
    left join Program p2 on p2.id=wsc2.program_id
    group by p2.id, wsc2.id) b on a.ProgramId = b.ProgramId

I haven't debugged this. But this kind of thing works well, especially if you include a

 where b.userSession = constant

clause