MySQL Query Help

kool_samule · February 1, 2012

I'm basically after the following:

Query to produce results of; invoice_item_price of all jobs that include a Typesetting task and all of those that don't.

The problem I'm having is that I can't figure out how to get the correct SUMtotal of the invoice_item_price, when searching through the tbl_task_item, to see if a Typesetting task has been completed. In trying to do this, it multiplies the invoice_item_price by the amount of tasks within a job.

Database:

-- MySQL dump 10.11
--
-- Host: localhost    Database: dbtemp
-- ------------------------------------------------------
-- Server version	5.0.83-community-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `tbl_analysis`
--

DROP TABLE IF EXISTS `tbl_analysis`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_analysis` (
  `analysis_id` int(20) unsigned NOT NULL auto_increment,
  `FK_job_id` int(20) unsigned default NULL,
  `analysis_gross` int(10) default NULL,
  `analysis_net` int(10) default NULL,
  PRIMARY KEY  (`analysis_id`),
  KEY `FK_job_id` (`FK_job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_analysis`
--

LOCK TABLES `tbl_analysis` WRITE;
/*!40000 ALTER TABLE `tbl_analysis` DISABLE KEYS */;
INSERT INTO `tbl_analysis` VALUES (1,1,100,50),(2,2,500,250);
/*!40000 ALTER TABLE `tbl_analysis` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_customer`
--

DROP TABLE IF EXISTS `tbl_customer`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_customer` (
  `cust_id` int(20) unsigned NOT NULL auto_increment,
  `cust_name` varchar(255) default NULL,
  PRIMARY KEY  (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_customer`
--

LOCK TABLES `tbl_customer` WRITE;
/*!40000 ALTER TABLE `tbl_customer` DISABLE KEYS */;
INSERT INTO `tbl_customer` VALUES (1,'Customer A');
/*!40000 ALTER TABLE `tbl_customer` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_invoice`
--

DROP TABLE IF EXISTS `tbl_invoice`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_invoice` (
  `invoice_id` int(20) unsigned NOT NULL auto_increment,
  `FK_cust_id` int(20) unsigned default NULL,
  `FK_proj_id` int(20) unsigned default NULL,
  `invoice_date` date default NULL,
  `invoice_total` varchar(30) default NULL,
  `invoice_status` enum('Pending','Sent') default NULL,
  PRIMARY KEY  (`invoice_id`),
  KEY `FK_cust_id` (`FK_cust_id`),
  KEY `FK_proj_id` (`FK_proj_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_invoice`
--

LOCK TABLES `tbl_invoice` WRITE;
/*!40000 ALTER TABLE `tbl_invoice` DISABLE KEYS */;
INSERT INTO `tbl_invoice` VALUES (1,1,1,'2010-01-30','500','Sent');
/*!40000 ALTER TABLE `tbl_invoice` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_invoice_item`
--

DROP TABLE IF EXISTS `tbl_invoice_item`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_invoice_item` (
  `invoice_item_id` int(20) unsigned NOT NULL auto_increment,
  `FK_job_id` int(20) unsigned default NULL,
  `FK_invoice_id` int(20) unsigned default NULL,
  `invoice_item_price` varchar(30) default NULL,
  `invoice_status` enum('Pending','Sent') default 'Pending',
  PRIMARY KEY  (`invoice_item_id`),
  KEY `FK_job_id` (`FK_job_id`),
  KEY `FK_invoice_id` (`FK_invoice_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_invoice_item`
--

LOCK TABLES `tbl_invoice_item` WRITE;
/*!40000 ALTER TABLE `tbl_invoice_item` DISABLE KEYS */;
INSERT INTO `tbl_invoice_item` VALUES (1,1,1,'250','Sent'),(2,2,1,'250','Sent');
/*!40000 ALTER TABLE `tbl_invoice_item` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_job`
--

DROP TABLE IF EXISTS `tbl_job`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_job` (
  `job_id` int(20) unsigned NOT NULL auto_increment,
  `FK_proj_id` int(20) unsigned default NULL,
  `FK_langt_id` int(20) unsigned default NULL,
  `job_title` varchar(255) default NULL,
  `job_pages` int(10) default '1',
  `job_complete` enum('Yes','No') default 'No',
  `job_complete_date` timestamp NULL default NULL,
  PRIMARY KEY  (`job_id`),
  KEY `FK_proj_id` (`FK_proj_id`),
  KEY `FK_langt_id` (`FK_langt_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_job`
--

LOCK TABLES `tbl_job` WRITE;
/*!40000 ALTER TABLE `tbl_job` DISABLE KEYS */;
INSERT INTO `tbl_job` VALUES (1,1,1,'Job 1',1,'Yes','2012-30-01 17:30:00'), (2,1,3,'Job 2',2,'Yes','2012-30-01 17:30:00');
/*!40000 ALTER TABLE `tbl_job` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_language`
--

DROP TABLE IF EXISTS `tbl_language`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_language` (
  `lang_id` int(20) unsigned NOT NULL auto_increment,
  `langname` varchar(255) default NULL,
  PRIMARY KEY  (`lang_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_language`
--

LOCK TABLES `tbl_language` WRITE;
/*!40000 ALTER TABLE `tbl_language` DISABLE KEYS */;
INSERT INTO `tbl_language` VALUES (1,'EN'),(2,'DE'),(3,'FR');
/*!40000 ALTER TABLE `tbl_language` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_project`
--

DROP TABLE IF EXISTS `tbl_project`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_project` (
  `proj_id` int(20) unsigned NOT NULL auto_increment,
  `proj_title` varchar(255) default NULL,
  `FK_cust_id` int(20) unsigned default NULL,
  `FK_langsid` int(20) unsigned default NULL,
  `proj_start_date` timestamp NULL default NULL,
  `proj_end_date` timestamp NULL default NULL,
  `proj_status` enum('Open','Hold','Enquiry','Redundant','Complete') default NULL,
  PRIMARY KEY  (`proj_id`),
  KEY `FK_cust_id` (`FK_cust_id`),
  KEY `FK_langsid` (`FK_langsid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_project`
--

LOCK TABLES `tbl_project` WRITE;
/*!40000 ALTER TABLE `tbl_project` DISABLE KEYS */;
INSERT INTO `tbl_project` VALUES (1,'Project A', 1,2,'2012-01-30 09:00:00','2012-01-30 17:30:00','Complete');
/*!40000 ALTER TABLE `tbl_project` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_task`
--

DROP TABLE IF EXISTS `tbl_task`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_task` (
  `task_id` int(20) unsigned NOT NULL auto_increment,
  `task_title` varchar(50) default NULL,
  PRIMARY KEY  (`task_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_task`
--

LOCK TABLES `tbl_task` WRITE;
/*!40000 ALTER TABLE `tbl_task` DISABLE KEYS */;
INSERT INTO `tbl_task` VALUES (1,'Preparation'),(2,'Typesetting'),(3,'Correction'),(4,'Evaluate');
/*!40000 ALTER TABLE `tbl_task` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_task_item`
--

DROP TABLE IF EXISTS `tbl_task_item`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_task_item` (
  `task_item_id` int(20) unsigned NOT NULL auto_increment,
  `FK_job_id` int(20) unsigned default NULL,
  `FK_task_id` int(20) unsigned default NULL,
  `status` enum('Pending','Ready','Sent','Active','Complete') default 'Pending',
  `task_complete_date` timestamp NULL default NULL,
  `task_order` int(20) unsigned default NULL,
  PRIMARY KEY  (`task_item_id`),
  KEY `FK_task_id` (`FK_task_id`),
  KEY `FK_job_id` (`FK_job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_task_item`
--

LOCK TABLES `tbl_task_item` WRITE;
/*!40000 ALTER TABLE `tbl_task_item` DISABLE KEYS */;
INSERT INTO `tbl_task_item` VALUES (1,1,1,'Complete','2012-01-30 10:00:00',1),(2,1,2,'Complete','2012-01-30 11:00:00',2),(3,1,3,'Complete','2012-01-30 12:00:00',3),(4,1,4,'Complete','2012-01-30 17:30:00',4),(5,2,1,'Complete','2012-01-30 10:00:00',1),(6,2,3,'Complete','2012-01-30 12:00:00',3),(7,2,4,'Complete','2012-01-30 17:30:00',4);
/*!40000 ALTER TABLE `tbl_task_item` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-01-30 18:59:47

QUERY:

SELECT 			DATE_FORMAT(proj_end_date, '%m') as month_number,
  				MONTHNAME(proj_end_date) AS the_month,
			YEAR(proj_end_date) AS the_year,
			SUM(analysis_net) as analysis_net,
			SUM(analysis_gross) as analysis_gross,
			SUM(job_pages) as job_pages,
			SUM(invoice_item_price) as total, 
CASE			        WHEN task_title='Typesetting'
			AND ti1.status='Complete'
			THEN 'Typeset'
			ELSE 'Non-Typeset'
			END AS 'JobType',
			langname
FROM 			tbl_job as j
INNER JOIN 			tbl_task_item as ti1
			ON ti1.FK_job_id = j.job_id
INNER JOIN 			tbl_task as t
			ON t.task_id = ti1.FK_task_id
INNER JOIN 			tbl_invoice_item as ii
			ON ii.FK_job_id = j.job_id
INNER JOIN 			tbl_invoice as i
			ON i.invoice_id=ii.FK_invoice_id
INNER JOIN 			tbl_analysis as a
			ON a.FK_job_id = j.job_id
INNER JOIN			tbl_language as l
			ON l.lang_id = j.FK_langt_id
INNER JOIN			tbl_project as p 				 				
			ON p.proj_id = j.FK_proj_id  
WHERE 			p.FK_cust_id = 1 				
			AND proj_status='Complete' 
GROUP BY			the_year, 				
			month_number, 				
			JobType, 				
			langname 
ORDER BY			the_year DESC, 				
			month_number DESC, 				
			JobType DESC, 				
			langname ASC

fenway · February 1, 2012

I'm confused by your design -- and why you're using non-aggregated fields in your SELECT list.

kool_samule · February 1, 2012

MySQL Design is broken down into seperate tables, for a pretty big database...what i've posted is a stripped down version, purely for this problem.

The SELECT query doesn't work as I want it to...it's just an example of what i've got so far...but the INNER JOINS obviously are required.

The JobType is determined by the task_title 'Typesetting', i.e. if the job has a Typesetting task, then the job as a whole is classed as a 'Typeset Job'.

The result I'm after is something to show the SUM of job_pages, analysis_gross, analysis_net and invoice_item_price per job.

The results need to be grouped by year/month/jobtype (Typeset/Non-Typeset)/langname

Is this possible?

fenway · February 1, 2012

Instead of grouping by the date parts separately, simply use YEARMONTH(), and then you can format it on output.

kool_samule · February 1, 2012

Thanks for the reply, however I'm getting an error: YEARMONTH does not exist.

The main problem I'm having is with the task_items / jobs, rather than the year/month/dates.

Hopefully this might explain the problem a bit better:

Job 1 has an invoice price of 250.

Job 1 has 4 tasks, 1 of which is Typesetting.

Job 2 has an invoice price of 250.

Job 2 has 3 tasks, 0 of which are Typesetting.

I want the query to produce a result of:

Typeset Job - 250 (Job 1 - has Typesetting Task)

Non Typeset Job - 250 (Job 2 - has no Typesetting Task)

Whats happening is:

Typeset Job - 250 (Job 1 - has 1 Typesetting Task)

Non Typeset Job - 750 (Job 1 - has 3 non-Typesetting Tasks)

Non Typeset Job - 750 (Job 2 - has 3 non-Typesetting Tasks)

Any ideas on this?

fenway · February 2, 2012

No, the problem you're having is with the dates -- because you've turned one column into two.

EXTRACT(YEAR_MONTH FROM yourDate).

kool_samule · February 2, 2012

Hi again, thanks for the response, I tried:

SELECT 		EXTRACT(YEAR_MONTH FROM proj_end_date),
		SUM(invoice_item_price) as total, 
CASE		WHEN task_title='Typesetting'
		AND ti1.status='Complete'
		THEN 'Typeset'
		ELSE 'Non-Typeset'
		END AS 'JobType'
FROM 		tbl_job as j
INNER JOIN 	tbl_task_item as ti1
		ON ti1.FK_job_id = j.job_id
INNER JOIN 	tbl_task as t
		ON t.task_id = ti1.FK_task_id
INNER JOIN 	tbl_invoice_item as ii
		ON ii.FK_job_id = j.job_id
INNER JOIN 	tbl_invoice as i
		ON i.invoice_id=ii.FK_invoice_id
INNER JOIN 	tbl_analysis as a
		ON a.FK_job_id = j.job_id
INNER JOIN	tbl_language as l
		ON l.lang_id = j.FK_langt_id
INNER JOIN	tbl_project as p 				 			
		ON p.proj_id = j.FK_proj_id  
WHERE 		p.FK_cust_id = 1 				
		AND proj_status='Complete' 
GROUP BY		JobType 
ORDER BY		JobType DESC

and this gives me:

EXTRACT(YEAR_MONTH FROM proj_end_date) - total - JobType
201201                                 - 250   - Typeset
201201                                 - 1500  - Non-Typeset

But the 'total' figures are not what I'm after...as mentioned in my previous posts, I want to class all jobs that have had a 'Typesetting' task as a Typeset job, and those that didn't as 'Non-Typeset'.

What is happening with the query at the moment is it is multiplying each task by the job invoice_item_price, rather than each job once.

fenway · February 2, 2012

That because you need to group by the YEAR_MONTH as well -- so simply alias that column, and all it to the GROUP BY clause.

kool_samule · February 2, 2012

Sorry, same result when using:

SELECT 			EXTRACT(YEAR_MONTH FROM proj_end_date) as job_date,
		SUM(invoice_item_price) as total, 
CASE		       	WHEN task_title='Typesetting'
		AND ti1.status='Complete'
		THEN 'Typeset'
		ELSE 'Non-Typeset'
		END AS 'JobType'
FROM 			tbl_job as j
INNER JOIN 		tbl_task_item as ti1
		ON ti1.FK_job_id = j.job_id
INNER JOIN 		tbl_task as t
		ON t.task_id = ti1.FK_task_id
INNER JOIN 		tbl_invoice_item as ii
		ON ii.FK_job_id = j.job_id
INNER JOIN 		tbl_invoice as i
		ON i.invoice_id=ii.FK_invoice_id
INNER JOIN 		tbl_analysis as a
		ON a.FK_job_id = j.job_id
INNER JOIN		tbl_language as l
		ON l.lang_id = j.FK_langt_id
INNER JOIN		tbl_project as p 				 			
		ON p.proj_id = j.FK_proj_id  
WHERE 			p.FK_cust_id = 1 				
		AND proj_status='Complete' 
GROUP BY		job_date, JobType 
ORDER BY		job_date, JobType DESC

I still feel the invoice_item_price / job / tasks are the issue though...

mikosiko · February 2, 2012

maybe?

SELECT EXTRACT(YEAR_MONTH FROM proj_end_date) as job_date,
             CASE WHEN task_title='Typesetting' AND ti1.status='Complete'
	      THEN SUM(invoice_item_price)
	      ELSE invoice_item_price
     END AS total,

CASE		       	WHEN task_title='Typesetting'
		AND ti1.status='Complete'
		THEN 'Typeset'
		ELSE 'Non-Typeset'
		END AS 'JobType'
FROM 			tbl_job as j
INNER JOIN 		tbl_task_item as ti1 ON ti1.FK_job_id = j.job_id
INNER JOIN 		tbl_task as t			ON t.task_id = ti1.FK_task_id
INNER JOIN 		tbl_invoice_item as ii			ON ii.FK_job_id = j.job_id
INNER JOIN 		tbl_invoice as i			ON i.invoice_id=ii.FK_invoice_id
INNER JOIN 		tbl_analysis as a 			ON a.FK_job_id = j.job_id
INNER JOIN		tbl_language as l			ON l.lang_id = j.FK_langt_id
INNER JOIN		tbl_project as p ON p.proj_id = j.FK_proj_id
WHERE 			p.FK_cust_id = 1
		AND proj_status='Complete'
GROUP BY		job_date, JobType
ORDER BY		job_date, JobType DESC;

fenway · February 2, 2012

If it's still an issue, the post the new output, they expected output, and I'll take a closer look.

kool_samule · February 3, 2012

OK, getting close...

QUERY:

SELECT 		EXTRACT(YEAR_MONTH FROM proj_end_date) as Jobdate,
CASE		WHEN task_title='Typesetting'
	AND ti1.status='Complete'
	THEN 'Typeset'
	ELSE 'Non-Typeset'
	END AS 'JobType',
CASE		WHEN task_title='Typesetting'
	AND ti1.status='Complete'
	THEN SUM(job_pages)
	ELSE job_pages
	END AS 'JobPages',
CASE		WHEN task_title='Typesetting'
	AND ti1.status='Complete'
	THEN SUM(analysis_gross)
	ELSE analysis_gross
	END AS 'AnalysisGross',
CASE		WHEN task_title='Typesetting'
	AND ti1.status='Complete'
	THEN SUM(analysis_net)
	ELSE analysis_net
	END AS 'AnalysisNet',
CASE WHEN 	task_title='Typesetting' AND ti1.status='Complete'
	THEN SUM(invoice_item_price)
	ELSE invoice_item_price
	END AS JobPrice
FROM 		tbl_job as j
INNER JOIN 	tbl_task_item as ti1 ON ti1.FK_job_id = j.job_id
INNER JOIN 	tbl_task as t ON t.task_id = ti1.FK_task_id
INNER JOIN 	tbl_invoice_item as ii ON ii.FK_job_id = j.job_id
INNER JOIN 	tbl_invoice as i ON i.invoice_id=ii.FK_invoice_id
INNER JOIN 	tbl_analysis as a ON a.FK_job_id = j.job_id
INNER JOIN	tbl_language as l ON l.lang_id = j.FK_langt_id
INNER JOIN	tbl_project as p ON p.proj_id = j.FK_proj_id
WHERE 		p.FK_cust_id = 1
	AND proj_status='Complete'
GROUP BY	JobDate, JobType
ORDER BY	JobDate, JobType DESC;

OUTPUT:

JobDate   - JobType   - JobPages - AnalysisGross - AnalysisNet - JobPrice
201201   - Typeset    - 1        - 100           - 50          - 250
201201   - Non-Typest - 1        - 100           - 50          - 250

EXPECTED OUTPUT:

JobDate   - JobType   - JobPages - AnalysisGross - AnalysisNet - JobPrice
201201   - Typeset    - 1        - 100           - 50          - 250
201201   - Non-Typest - 2        - 500           - 250         - 250

Thank you for the help!

mikosiko · February 3, 2012

just to validate a couple things:

a) What kind of relationship is between tbl_JOBS and tbl_Analysis? 1:n or 1:1 ? ... if the relationship is 1:1 as it seems to be, you don't need to SUM() the columns analysis_gross nor analysis_net.

b) run this query and post the results:

SELECT b.FK_job_id,
       a.task_id,
       a.task_title,
       b.status AS TaskItem_Status
FROM tbl_task AS a
     JOIN tbl_task_item AS b ON a.task_id = b.FK_task_id
ORDER BY b.FK_job_id, a.task_id;

c) run this query and check if the results are consistent with the results of b)

SELECT  j.job_id As Job,
        EXTRACT(YEAR_MONTH FROM proj_end_date) as Jobdate,

        CASE WHEN   task_title='Typesetting' AND ti1.status='Complete'
             THEN 'Typesetting'
             ELSE 'Non-Typest'
             END AS 'JobType',

        CASE WHEN 	task_title='Typesetting' AND ti1.status='Complete'
            THEN SUM(j.job_pages)
            ELSE j.job_pages
            END AS JobPages,

        a.analysis_gross AS 'AnalysisGross',
        a.analysis_net AS 'AnalysisNet',
        
        CASE WHEN   task_title='Typesetting' AND ti1.status='Complete'
            THEN SUM(ii.invoice_item_price)
            ELSE ii.invoice_item_price
            END AS JobPrice

FROM  tbl_job as j
      INNER JOIN 	tbl_task_item as ti1   ON ti1.FK_job_id = j.job_id
      INNER JOIN 	tbl_task as t          ON t.task_id = ti1.FK_task_id
      INNER JOIN 	tbl_invoice_item as ii ON ii.FK_job_id = j.job_id
      INNER JOIN 	tbl_invoice as i       ON i.invoice_id=ii.FK_invoice_id
      INNER JOIN 	tbl_analysis as a      ON a.FK_job_id = j.job_id
      INNER JOIN	 tbl_project as p       ON p.proj_id = j.FK_proj_id
WHERE p.FK_cust_id = 1
  AND p.proj_status='Complete'
GROUP BY 	Job, JobDate, JobType
ORDER BY 	Job, JobDate, JobType DESC;

kool_samule · February 3, 2012

OK, getting really close now...

a) Relationship is 1:1

b)

+-----------+---------+-------------+-----------------+
| FK_job_id | task_id | task_title  | TaskItem_Status |
+-----------+---------+-------------+-----------------+
|         1 |       1 | Preparation | Complete        |
|         1 |       2 | Typesetting | Complete        |
|         1 |       3 | Correction  | Complete        |
|         1 |       4 | Evaluate    | Complete        |
|         2 |       1 | Preparation | Complete        |
|         2 |       3 | Correction  | Complete        |
|         2 |       4 | Evaluate    | Complete        |
+-----------+---------+-------------+-----------------+
7 rows in set (0.07 sec)

c)

+-----+---------+-------------+----------+---------------+-------------+----------+
| Job | Jobdate | JobType     | JobPages | AnalysisGross | AnalysisNet | JobPrice |
+-----+---------+-------------+----------+---------------+-------------+----------+
|   1 |  201201 | Typesetting |        1 |           100 |          50 | 250      |
|   1 |  201201 | Non-Typest  |        1 |           100 |          50 | 250      |
|   2 |  201201 | Non-Typest  |        2 |           500 |         250 | 250      |
+-----+---------+-------------+----------+---------------+-------------+----------+
3 rows in set (0.21 sec)

c) is the best one so far...just need to remove:

|   1 |  201201 | Non-Typest  |        1 |           100 |          50 | 250      |

mikosiko · February 3, 2012

c) is the best one so far...just need to remove:
Code: [select]

| 1 | 201201 | Non-Typest | 1 | 100 | 50 | 250 |

under which condition? at first glance doesn't look possible in the current query (I could be wrong)... but... that is for you to work on... 2 hints for an alternative solution .... 1) Divide to Reign... 2) UNION

kool_samule · February 3, 2012

Because Job 1 has a Typesetting Task, I dont want it included in Non-Typesetting.

The current query isnt doing it, but that's the result I'm after..

mikosiko · February 3, 2012

apply the 2 hints then... try... it should work ... just re-think the query a little starting with what you have now... remember... divide ... union ... that is one way to do it

kool_samule · February 7, 2012

OK, I'm stuck...I've tried to alter the SELECT query, with no joy...

I don't know how Divide would help here...

and I can't figure out a way to differentiate between jobs that have a Typesetting task and those that don't...without getting the above results.

Would a SubQuery do the trick?

mikosiko · February 7, 2012

ok... here is one way to get the results that you were looking for... (DIVIDE the problem and UNION):

SELECT  j.job_id As Job,
        EXTRACT(YEAR_MONTH FROM proj_end_date) as Jobdate,
        'Typesetting' AS 'JobType',
        SUM(j.job_pages) AS 'JobPages',
        a.analysis_gross AS 'AnalysisGross',
        a.analysis_net AS 'AnalysisNet',
        SUM(ii.invoice_item_price) AS JobPrice
FROM  tbl_job as j
      INNER JOIN 	tbl_task_item as ti1   ON ti1.FK_job_id = j.job_id
      INNER JOIN 	tbl_task as t          ON t.task_id = ti1.FK_task_id AND t.task_title='Typesetting'
      INNER JOIN 	tbl_invoice_item as ii ON ii.FK_job_id = j.job_id
      INNER JOIN 	tbl_invoice as i       ON i.invoice_id=ii.FK_invoice_id
      INNER JOIN 	tbl_analysis as a      ON a.FK_job_id = j.job_id
      INNER JOIN	 tbl_project as p       ON p.proj_id = j.FK_proj_id
WHERE p.FK_cust_id = 1
  AND proj_status='Complete'


UNION

SELECT  j.job_id As Job,
        EXTRACT(YEAR_MONTH FROM proj_end_date) as Jobdate,
        'Non-Typest' AS 'JobType',
        SUM(j.job_pages) AS 'JobPages',
        a.analysis_gross AS 'AnalysisGross',
        a.analysis_net AS 'AnalysisNet',
        ii.invoice_item_price AS JobPrice
FROM  tbl_job as j
      INNER JOIN 	tbl_task_item as ti1   ON ti1.FK_job_id = j.job_id
      INNER JOIN 	tbl_task as t          ON t.task_id = ti1.FK_task_id AND t.task_title !='Typesetting'
      INNER JOIN 	tbl_invoice_item as ii ON ii.FK_job_id = j.job_id
      INNER JOIN 	tbl_invoice as i       ON i.invoice_id=ii.FK_invoice_id
      INNER JOIN 	tbl_analysis as a      ON a.FK_job_id = j.job_id
      INNER JOIN	 tbl_project as p       ON p.proj_id = j.FK_proj_id
WHERE p.FK_cust_id = 1
  AND proj_status ='Complete'
  AND j.job_id NOT IN (SELECT  x.job_id
                         FROM  tbl_job as x
                               INNER JOIN 	tbl_task_item as tti   ON tti.FK_job_id = x.job_id
                               INNER JOIN   tbl_task as tt          ON tt.task_id = tti.FK_task_id AND tt.task_title ='Typesetting')

GROUP BY	Job, JobDate,JobType
ORDER BY	Job,JobDate, JobType DESC;

and the results:

Job Jobdate JobType JobPages AnalysisGross AnalysisNet JobPrice
1 201201 'Typesetting' 1 100 50 250

2 201201 'Non-Typest' 6 500 250 250

kool_samule · February 8, 2012

Perfect, thanks a lot...would never have got there without your help.

Much appreciated...

Memory bank: updated.

Sign In

MySQL Query Help

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information