Jump to content

Unable to Integrate Most Recent Date Query into Query With Multiple Joins

JipThePeople

By JipThePeople
in MySQL Help

 Share

Recommended Posts

JipThePeople Member

JipThePeople

Posted
Posted

I have the following query (no issues) and now I need to add code to SELECT additional data from another table.

 

The big picture is that a file is assigned a score which is written to the file_grade_answers table. The most recent score needs to be retrieved, which I can do but I can't integrate this query into the original query.

 

For reference:

MySQL client version: mysqlnd 5.0.7-dev - 091210 - $Revision: 304625 $

 

Here is the original query which retrieves misc data about a file from multiple tables via JOINS:

SELECT `t1`.`id`, `t1`.`filename`, `t1`.`filegrade_id`, `t1`.`percent_id`, `t2`.`process` 
FROM (`proposal_files` AS `t1` JOIN `prop_file_processes` AS `t2` ON `t1`.`process` = `t2`.`id`) 
WHERE `t1`.`propid` = '209'
GROUP BY t1.id 
ORDER BY t1.filename ASC

 

I have a new table (file_grade_answers) that contains records and I need to only select the most recent record. I have a query that will successfully retrieve the most recent record for each "file_id", but my issue is I don't know how to integrate it into my original query. However, this query will need tweaking to make it work in context of the original query. That is the snag for me. I don't know how to make it only retrieve one record in context of the complex JOIN in the original query.

 

When I tried to integrate it, I got the #1241 - Operand should contain 1 column(s) error, and that is because it IS returning multiple records (the most recent record per each file id).

 

This query will successfully retrieve one record (most recent record per "submit_date" col) per "file_id", and it will return three records since there are three unique file IDs in the records. NOTE: There are duplicate file IDs because a history must be maintained every time a file is assigned a score.

SELECT a.file_id, a.score, a.submit_date
FROM `file_grade_answers` AS a
LEFT JOIN `file_grade_answers` AS b ON ( (
a.file_id
) = ( b.file_id )
AND a.submit_date < b.submit_date )
WHERE b.submit_date IS NULL

 

 

Creating the `file_grade_answers` table:

CREATE TABLE IF NOT EXISTS `file_grade_answers` (
  `filegrade_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `file_id` int(10) unsigned NOT NULL,
  `reviewer_id` varchar(100) NOT NULL,
  `owner_at_review` varchar(100) NOT NULL,
  `process_at_review` int(10) unsigned NOT NULL,
  `question_ids` varchar(100) NOT NULL,
  `answers` varchar(100) NOT NULL,
  `recommendations` blob NOT NULL,
  `submit_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `possible_points` int(10) unsigned NOT NULL,
  `actual_points` int(10) unsigned NOT NULL,
  `score` int(10) unsigned NOT NULL,
  PRIMARY KEY (`filegrade_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

--
-- Dumping data for table `file_grade_answers`
--

INSERT INTO `file_grade_answers` (`filegrade_id`, `file_id`, `reviewer_id`, `owner_at_review`, `process_at_review`, `question_ids`, `answers`, `recommendations`, `submit_date`, `possible_points`, `actual_points`, `score`) VALUES
(1, 451, 'smith', 'smith', 2, '1,2,3,4', '2,1,1,2', 0x54657374696e6720313233, '2011-08-24 09:27:33', 20, 6, 30),
(2, 453, 'smith', 'smith', 11, '1,2,3', '4,4,4', '', '2011-08-24 10:58:05', 15, 12, 80),
(3, 452, 'smith', 'smith', 5, '1,2,3,4,5,6,7', '3,2,1,1,2,3,1', '', '2011-08-24 10:59:39', 35, 13, 37),
(4, 452, 'smith', 'smith', 5, '1,2,3,4,5,6,7', '4,5,4,5,5,5,3', '', '2011-08-25 09:39:39', 35, 31, 89),
(5, 451, 'smith', 'smith', 2, '1,2', '4,5', '', '2011-08-25 10:29:13', 10, 9, 90),
(6, 452, 'smith', 'smith', 5, '1,2', '3,3', '', '2011-08-26 08:09:20', 10, 6, 60),
(7, 452, 'smith', 'smith', 5, '1', '3', '', '2011-08-26 08:09:45', 5, 3, 60),
(8, 452, 'smith', 'smith', 10, '1,2,3,4', '5,5,5,5', 0x546869732066696c652069732067726561746e6573732e, '2011-08-29 12:46:20', 20, 20, 100),
(9, 453, 'smith', 'smith', 11, '1,2,3,4,6', '4,5,4,5,5', 0x536f6d657468696e672068617320676f6e652061206269742061736b6577206f6e207468652074726564646c652e, '2011-08-29 20:42:56', 25, 23, 92),
(10, 451, 'smith', 'smith', 2, '1,2,3,4,5,6,7', '1,2,1,3,5,6,2', 0x4d79207265636f6d6d656e646174696f6e2074657874, '2011-09-06 14:16:23', 35, 20, 57),
(11, 451, 'smith', 'smith', 2, '1,2', '2,1', '', '2011-09-08 13:28:44', 10, 3, 30),
(12, 452, 'smith', 'smith', 10, '1,2,3,4', '4,1,2,1', '', '2011-09-08 13:29:09', 20, 8, 40),
(13, 453, 'smith', 'smith', 11, '1', '1', '', '2011-09-08 13:29:23', 5, 1, 20);

 

 

Creating Process table:

  
CREATE TABLE IF NOT EXISTS `prop_file_processes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `process` varchar(100) NOT NULL DEFAULT '',
  `sortorder` int(2) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

--
-- Dumping data for table `prop_file_processes`
--

INSERT INTO `prop_file_processes` (`id`, `process`, `sortorder`) VALUES
(8, 'In Production', 9),
(2, 'Writing', 1),
(3, 'Ready for Edit', 3),
(4, 'Editing File', 4),
(5, 'Ready for Review', 5),
(6, 'Final', 11),
(7, 'Other Collaboration', 2),
(10, 'In Review 1', 6),
(11, 'In Review 2, 7),
(12, 'In Review 3', ,
(13, 'Signature Required', 10);

 

 

Proposal File Table:

CREATE TABLE IF NOT EXISTS `proposal_files` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `propid` int(10) unsigned NOT NULL DEFAULT '0',
  `filename` varchar(255) NOT NULL DEFAULT '',
  `inout` int(10) unsigned NOT NULL DEFAULT '0',
  `folder` int(10) unsigned NOT NULL DEFAULT '0',
  `process` int(10) unsigned NOT NULL DEFAULT '0',
  `userid` varchar(100) NOT NULL DEFAULT '',
  `owner` varchar(100) NOT NULL DEFAULT '',
  `filesize` varchar(50) DEFAULT NULL,
  `pagecount` int(10) unsigned DEFAULT NULL,
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `filegrade_id` int(10) DEFAULT NULL,
  `percent_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

--
-- Dumping data for table `proposal_files`
--

INSERT INTO `proposal_files` (`id`, `propid`, `filename`, `inout`, `folder`, `process`, `userid`, `owner`, `filesize`, `pagecount`, `created`, `last_updated`, `filegrade_id`, `percent_id`) VALUES
(464, 209, 'other_architecture.doc', 1, 422, 2, 'smith', 'smith', '237568', 5, '2011-05-04 14:27:43', '2011-05-04 14:27:43', NULL, NULL),
(463, 203, 'over.doc', 0, 366, 3, '', 'smith', '1468416', 65, '2011-03-29 09:08:37', '2011-03-29 09:08:37', NULL, NULL),
(462, 203, 'tictoc.doc', 0, 366, 2, '', 'smith', '54784', 7, '2011-03-29 09:08:33', '2011-03-29 09:08:33', NULL, NULL),
(460, 203, 'generic.docx', 0, 424, 2, '', 'smith', '88591', 1, '2011-03-29 09:08:29', '2011-03-29 09:08:29', NULL, NULL),
(456, 209, 'bb.docx', 0, 414, 11, '', 'smith', '27646', 7, '2011-03-28 16:47:02', '2011-03-28 16:47:02', NULL, NULL),
(455, 209, 'rb-processes.dotx', 0, 422, 5, '', 'smith', '28575', NULL, '2011-03-28 16:47:02', '2011-03-28 16:47:02', NULL, NULL),
(454, 209, 'dds-bbs.docx', 0, 414, 8, '', 'smith', '34191', 3, '2011-03-28 16:46:59', '2011-03-28 16:46:59', NULL, NULL),
(453, 209, 'dds-7.docx', 0, 414, 11, '', 'smith', '15860', 3, '2011-03-28 16:46:57', '2011-03-28 16:46:57', NULL, NULL),
(457, 203, 'troubleshooting-ds.docx', 0, 366, 7, '', 'smith', '25267', 1, '2011-03-29 09:08:21', '2011-03-29 09:08:21', NULL, NULL),
(452, 209, 'troubleshooting-dl.docx', 0, 414, 10, '', 'smith', '25267', 1, '2011-03-28 16:46:55', '2011-03-28 16:46:55', NULL, NULL),
(451, 209, 'blue-dds.doc', 1, 422, 2, 'smith', 'smith', '1468416', 65, '2011-03-28 16:46:04', '2011-05-04 09:16:39', NULL, NULL),
(461, 203, 'bb.docx', 0, 424, 7, '', 'smith', '27646', 7, '2011-03-29 09:08:31', '2011-03-29 09:08:31', NULL, NULL),
(459, 203, 'Process.docx', 0, 366, 2, '', 'smith', '34191', 3, '2011-03-29 09:08:27', '2011-03-29 09:08:27', NULL, NULL),
(458, 203, 'md-dds-1.docx', 0, 366, 2, '', 'smith', '15860', 3, '2011-03-29 09:08:25', '2011-03-29 09:08:25', NULL, NULL),
(465, 211, 'over-hills.doc', 0, 433, 2, '', 'smith', '1468416', 65, '2011-08-29 12:52:10', '2011-08-29 12:52:10', NULL, NULL),
(466, 209, 'croctoc.doc', 0, 414, 2, '', 'smith', '54784', 7, '2011-08-29 12:54:35', '2011-08-29 12:54:35', NULL, NULL),
(467, 209, 'md-2-Process.docx', 0, 414, 2, '', 'smith', '1288683', 64, '2011-08-29 12:54:40', '2011-08-29 12:54:40', NULL, NULL),
(468, 209, 'more-toc.doc', 0, 414, 2, '', 'smith', '54784', 7, '2011-08-29 12:54:44', '2011-08-29 12:54:44', NULL, NULL),
(469, 209, 'DDS-TEMPLATE.dotx', 0, 414, 2, '', 'smith', '30239', NULL, '2011-08-29 12:54:47', '2011-08-29 12:54:47', NULL, NULL);

 

Create the proposal info table (I know the names are crazy, but this is dummy data on a test server):

CREATE TABLE IF NOT EXISTS `proposal_info` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL DEFAULT '',
  `code_name` varchar(100) NOT NULL DEFAULT '',
  `description` blob NOT NULL,
  `notes` blob,
  `start_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `submit_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `close_date` datetime DEFAULT NULL,
  `status` int(10) NOT NULL DEFAULT '0',
  `region` int(10) NOT NULL DEFAULT '0',
  `pm` varchar(100) NOT NULL DEFAULT '',
  `pa` varchar(100) DEFAULT '',
  `le` varchar(100) DEFAULT '',
  `designer` varchar(100) DEFAULT '',
  `cse` varchar(100) NOT NULL DEFAULT '',
  `sa` varchar(100) DEFAULT '',
  `bd` varchar(100) DEFAULT NULL,
  `wk_planner` varchar(100) DEFAULT NULL,
  `pursuit_manager` varchar(50) DEFAULT NULL,
  `bid_manager` varchar(50) DEFAULT NULL,
  `pursuit_id` varchar(100) DEFAULT NULL,
  `total_cost` varchar(100) DEFAULT '',
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `supp_files_pg` varchar(100) NOT NULL DEFAULT 'pgs_files_support.php',
  `archived` int(1) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=212 ;

--
-- Dumping data for table `proposal_info`
--

INSERT INTO `proposal_info` (`id`, `name`, `code_name`, `description`, `notes`, `start_date`, `submit_date`, `close_date`, `status`, `region`, `pm`, `pa`, `le`, `designer`, `cse`, `sa`, `bd`, `wk_planner`, `pursuit_manager`, `bid_manager`, `pursuit_id`, `total_cost`, `created`, `supp_files_pg`, `archived`) VALUES
(211, 'Kiev', 'Kiev', 0x426f67757320746573742070726f6a656374, '', '2011-07-01 00:00:00', '2012-07-01 00:00:00', NULL, 3, 4, '', '', '', 'smith', 'smith', '', '', 'Vasilly Zaitzsev', '', '', '', '$1B', '2011-07-25 10:21:37', 'pgs_files_support_v2.php', NULL),
(210, 'Spider Lockhart', 'Spider Lockhart', 0x54657374, '', '2011-05-19 00:00:00', '2014-05-19 00:00:00', '2011-05-18 00:00:00', 4, 4, '', '', '', '', 'smith', '', '', '', '', '', '', '', '2011-05-19 10:22:20', 'pgs_files_support_v2.php', NULL),
(209, 'Kursk', 'Operation Citadel', 0x54455354204949, '', '2011-03-01 00:00:00', '2012-06-01 00:00:00', NULL, 1, 3, '', '', '', '', 'mathjosh', '', '', '', '', '', '', '', '2011-03-11 10:20:32', 'pgs_files_support_v2.php', NULL),
(208, 'P.N. Guinn', 'Mangy Bird', 0x54455354, '', '2011-03-10 00:00:00', '2012-06-10 00:00:00', NULL, 4, 6, '', '', '', '', 'mathjosh', '', '', '', '', '', '', '', '2011-03-11 10:15:42', 'pgs_files_support_v2.php', NULL),
(207, 'Splunge', 'Monkey Butt', 0x54657374, '', '2011-03-01 00:00:00', '2011-05-01 00:00:00', NULL, 3, 3, '', '', '', '', 'mathjosh', '', '', '', '', '', '', '$2', '2011-03-08 09:42:58', 'pgs_files_support_v2.php', NULL),
(206, 'Glamis', 'Baumgardener', 0x54455354, '', '2011-03-31 00:00:00', '2011-07-31 00:00:00', NULL, 4, 4, '', '', '', '', 'smith', '', '', '', '', '', '', '', '2011-03-07 12:31:52', 'pgs_files_support_v2.php', NULL),
(200, 'Manstein', 'Operation Winter Storm', 0x4e65772070726f6a656374, '', '2011-02-16 00:00:00', '2011-05-16 00:00:00', NULL, 3, 6, '', '', '', '', 'smith', '', '', '', '', '', '', '', '2011-02-16 08:09:32', 'pgs_files_support_v2.php', NULL),
(203, 'AG South', 'Karkov', 0x54455354, '', '2011-02-01 00:00:00', '2011-04-01 00:00:00', '2011-05-08 00:00:00', 4, 4, '', '', '', '', 'smith', '', '', '', '', '', '', '', '2011-02-22 10:17:18', 'pgs_files_support_v2.php', NULL),
(201, 'Eastern Front 1941', 'Operation Barabarossa', 0x426f6775732070726f6a656374, '', '2011-02-17 00:00:00', '2012-06-17 00:00:00', NULL, 12, 4, '', '', '', '', 'smith', '', '', '', '', '', '', '', '2011-02-16 08:40:58', 'pgs_files_support_v2.php', NULL),
(202, 'T34', 'Kursk', 0x426f677573, '', '2011-02-01 00:00:00', '2011-05-01 00:00:00', NULL, 5, 1, '', '', '', '', 'smith', '', '', '', '', '', '', '', '2011-02-16 10:32:21', 'pgs_files_support_v2.php', NULL),
(204, 'Ritchie Blackmore', 'LA Connection', 0x54455354, '', '2011-02-23 00:00:00', '2012-03-23 00:00:00', NULL, 2, 4, '', '', '', '', 'smith', '', '', '', '', '', '', '', '2011-02-25 12:47:42', 'pgs_files_support_v2.php', NULL),
(205, 'Bub Frawley', 'Snitterton Lewis', 0x427562, '', '2011-03-02 00:00:00', '2012-06-02 00:00:00', NULL, 4, 1, '', '', '', '', 'smith', '', '', '', '', '', '', '', '2011-03-02 13:22:59', 'pgs_files_support_v2.php', NULL);

 

 

Any help will be greatly appreciated.

Link to comment
Share on other sites
JipThePeople Member

JipThePeople

Posted
  • Author
Posted

Why the GROUP BY in the first query?

 

I did not write the original query and since the file id fields are always unique, I don't know why you would include a GROUP BY either. This query is used to retrieve data about all files for a given prop_id. It will populate an html table with info on each file.

Link to comment
Share on other sites
JipThePeople Member

JipThePeople

Posted
  • Author
Posted

Then you just need to join in the second query as a derived table, using filegrade_id as the join condition -- but drop the GROUP BY.

 

You didn't write the query you're asking for help with?  But you're confident changing it?

 

I didn't write the original query so I am not sure what the other guy was thinking regarding the GROUP BY which is what I stated earlier.

 

I am seeking help here because I am not an SQL expert and I do appreciate your time and help.

 

Regarding your recommendation (need to join in the second query as a derived table, using filegrade_id as the join condition -- but drop the GROUP BY), can you please provide an example? I have exhausted my ideas and the syntax is one of my main issues when trying to make these two queries work together. I have also not worked with derived tables before. Thx again for your time.

Link to comment
Share on other sites
fenway Advanced Member

fenway

Posted
Posted 

SELECT 
`t1`.`id`, `t1`.`filename`, `t1`.`filegrade_id`, `t1`.`percent_id`, `t2`.`process` 
FROM `proposal_files` AS `t1` 
JOIN `prop_file_processes` AS `t2` ON ( `t1`.`process` = `t2`.`id`) 
INNER JOIN
(
SELECT a.filegrade_id, a.file_id, a.score, a.submit_date
FROM `file_grade_answers` AS a
LEFT JOIN `file_grade_answers` AS b ON ( (
a.file_id
) = ( b.file_id )
AND a.submit_date < b.submit_date )
WHERE b.submit_date IS NULL
) AS t3 on ( t1.filegrade_id = t3.filegrade_id )
WHERE `t1`.`propid` = '209'
ORDER BY t1.filename ASC

Untested -- that's the idea.

Link to comment
Share on other sites
JipThePeople Member

JipThePeople

Posted
  • Author
Posted 

SELECT 
`t1`.`id`, `t1`.`filename`, `t1`.`filegrade_id`, `t1`.`percent_id`, `t2`.`process` 
FROM `proposal_files` AS `t1` 
JOIN `prop_file_processes` AS `t2` ON ( `t1`.`process` = `t2`.`id`) 
INNER JOIN
(
SELECT a.filegrade_id, a.file_id, a.score, a.submit_date
FROM `file_grade_answers` AS a
LEFT JOIN `file_grade_answers` AS b ON ( (
a.file_id
) = ( b.file_id )
AND a.submit_date < b.submit_date )
WHERE b.submit_date IS NULL
) AS t3 on ( t1.filegrade_id = t3.filegrade_id )
WHERE `t1`.`propid` = '209'
ORDER BY t1.filename ASC

Untested -- that's the idea.

 

I will give it a shot. Thx.

Link to comment
Share on other sites
JipThePeople Member

JipThePeople

Posted
  • Author
Posted 

SELECT 
`t1`.`id`, `t1`.`filename`, `t1`.`filegrade_id`, `t1`.`percent_id`, `t2`.`process` 
FROM `proposal_files` AS `t1` 
JOIN `prop_file_processes` AS `t2` ON ( `t1`.`process` = `t2`.`id`) 
INNER JOIN
(
SELECT a.filegrade_id, a.file_id, a.score, a.submit_date
FROM `file_grade_answers` AS a
LEFT JOIN `file_grade_answers` AS b ON ( (
a.file_id
) = ( b.file_id )
AND a.submit_date < b.submit_date )
WHERE b.submit_date IS NULL
) AS t3 on ( t1.filegrade_id = t3.filegrade_id )
WHERE `t1`.`propid` = '209'
ORDER BY t1.filename ASC

Untested -- that's the idea.

 

Thx for the help and the time spent. Unfortunately, it yielded an empty result set (i.e., zero rows).

 

The code below (an excerpt taken from your latest recommended code) yields three records correctly (per most recent submit date) when executed separately:

 

SELECT a.filegrade_id, a.file_id, a.score, a.submit_date

FROM `file_grade_answers` AS a

LEFT JOIN `file_grade_answers` AS b ON ( (

a.file_id

) = ( b.file_id )

AND a.submit_date < b.submit_date )

WHERE b.submit_date IS NULL

 

But the entire code when executed together does not yields any records.

 

Any suggestions?

Link to comment
Share on other sites
JipThePeople Member

JipThePeople

Posted
  • Author
Posted

Well, I suppose it should be flipped -- derived table should be first, and then LEFT JOINed to the details, in case there aren't any.

 

But that begs the question -- are sure that there are matching rows?

 

Yes, there are matching rows representing the most recent score for each unique file id.

 

So if I remove that portion of the code, we are left with this.

 

SELECT 
`t1`.`id`, `t1`.`filename`, `t1`.`filegrade_id`, `t1`.`percent_id`, `t2`.`process` 
FROM `proposal_files` AS `t1` 
JOIN `prop_file_processes` AS `t2` ON ( `t1`.`process` = `t2`.`id`) 
WHERE `t1`.`propid` = '209'
GROUP BY t1.id
ORDER BY t1.filename ASC

 

Can you please give an example of flipping the derived table in the query? Also I determined that it was necessary to have the GROUP BY in the query or else it would only return a single record.

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.