WAMFT1 Posted October 16, 2015 Share Posted October 16, 2015 Hi all, I am trying to create a query to pull all information from one table and just the last record for each prospect_id. I have been able to create the query in MSAccess (below) but cannot work out how to get this to work in php. Can someone please help? In access this works but to run on SQL it does not return any data. The problem I believe lies around the LAST request. SELECT DATABASE_Prospects.FirstName, DATABASE_Prospects.LastName, DATABASE_Prospects.State, DATABASE_Prospects.Active, DATABASE_Prospects.prospect_id, DATABASE_Prospects.dlr_group, DATABASE_Prospects.Income, DATABASE_Prospects.Probability, DATABASE_Prospects.added, Last(DATABASE_Prospect_Notes.comment) AS LastOfcomment FROM DATABASE_Prospect_Notes RIGHT JOIN DATABASE_Prospects ON DATABASE_Prospect_Notes.prospect_id = DATABASE_Prospects.prospect_id GROUP BY DATABASE_Prospects.FirstName, DATABASE_Prospects.LastName, DATABASE_Prospects.State, DATABASE_Prospects.Active, DATABASE_Prospects.prospect_id, DATABASE_Prospects.dlr_group, DATABASE_Prospects.Income, DATABASE_Prospects.Probability, DATABASE_Prospects.added HAVING (((DATABASE_Prospects.Active)=1)); Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 16, 2015 Share Posted October 16, 2015 (edited) Some Formatting would make it much more readable. Post an sql dump of your tables and data for us to test on. Mysql doesnt not have a "Last" function SELECT database_prospects.firstname, database_prospects.lastname, database_prospects.state, database_prospects.active, database_prospects.prospect_id, database_prospects.dlr_group, database_prospects.income, database_prospects.probability, database_prospects.added, Last(database_prospect_notes.comment) AS LastOfcomment FROM database_prospect_notes RIGHT JOIN database_prospects ON database_prospect_notes.prospect_id = database_prospects.prospect_id GROUP BY database_prospects.firstname, database_prospects.lastname, database_prospects.state, database_prospects.active, database_prospects.prospect_id, database_prospects.dlr_group, database_prospects.income, database_prospects.probability, database_prospects.added HAVING (( ( database_prospects.active ) = 1 )) Edited October 16, 2015 by benanamen Quote Link to comment Share on other sites More sharing options...
WAMFT1 Posted October 16, 2015 Author Share Posted October 16, 2015 Attached is a link to the SQL dump, thanks for your help. Hopefully there will be a work around. http://www.wamft.com.au/prospects.sql Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 16, 2015 Share Posted October 16, 2015 (edited) Here is the MySQL version to get you up and running. You should always use lowercase column names. You have some all lower, some upper and lower SQL SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for database_prospect_notes -- ---------------------------- DROP TABLE IF EXISTS `database_prospect_notes`; CREATE TABLE `database_prospect_notes` ( `note_id` int(11) NOT NULL AUTO_INCREMENT, `prospect_id` int(11) NOT NULL, `staff_id` int(11) NOT NULL, `comment` mediumtext NOT NULL, `date` datetime NOT NULL, PRIMARY KEY (`note_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of database_prospect_notes -- ---------------------------- INSERT INTO `database_prospect_notes` VALUES ('1', '1', '1', 'Police check uncovered issues?', '2015-10-15 12:02:33'); INSERT INTO `database_prospect_notes` VALUES ('2', '1', '236', 'Appointed', '2015-10-15 14:29:27'); -- ---------------------------- -- Table structure for database_prospects -- ---------------------------- DROP TABLE IF EXISTS `database_prospects`; CREATE TABLE `database_prospects` ( `prospect_id` int(11) NOT NULL AUTO_INCREMENT, `FirstName` varchar(20) NOT NULL, `LastName` varchar(20) NOT NULL, `State` varchar(3) NOT NULL, `Active` int(1) NOT NULL, `Email` varchar(100) NOT NULL, `UserType` varchar( NOT NULL DEFAULT 'Prospect', `dlr_group` varchar(10) NOT NULL, `middlename` varchar(30) NOT NULL, `carname` varchar(80) NOT NULL, `tradingname` varchar(80) NOT NULL, `arn` int(6) NOT NULL, `carn` int(6) NOT NULL, `authorisations` varchar(20) NOT NULL, `phonehome` varchar(15) NOT NULL, `phonework` varchar(15) NOT NULL, `phonemobile` varchar(15) NOT NULL, `faxwork` varchar(15) NOT NULL, `website` varchar(80) NOT NULL, `paddress` varchar(50) NOT NULL, `psuburb` varchar(30) NOT NULL, `pstate` varchar(3) NOT NULL, `ppostcode` varchar(4) NOT NULL, `haddress` varchar(50) NOT NULL, `hsuburb` varchar(30) NOT NULL, `hstate` varchar(3) NOT NULL, `hpostcode` varchar(4) NOT NULL, `baddress` varchar(50) NOT NULL, `bsuburb` varchar(30) NOT NULL, `bstate` varchar(3) NOT NULL, `bpostcode` varchar(4) NOT NULL, `raddress` varchar(50) NOT NULL, `rsuburb` varchar(30) NOT NULL, `rstate` varchar(3) NOT NULL, `rpostcode` varchar(4) NOT NULL, `dob` date DEFAULT NULL, `placeofbirth` varchar(50) NOT NULL, `split` varchar(20) NOT NULL, `abn` varchar(15) NOT NULL, `exdealer` varchar(40) NOT NULL, `software` varchar(30) NOT NULL, `softwaremodules` varchar(30) NOT NULL, `research` varchar(30) NOT NULL, `Income` varchar(20) NOT NULL, `Probability` varchar(4) NOT NULL, `Cashflow` varchar(20) NOT NULL, `added` date NOT NULL, PRIMARY KEY (`prospect_id`), KEY `id_2` (`prospect_id`), KEY `id` (`prospect_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of database_prospects -- ---------------------------- INSERT INTO `database_prospects` VALUES ('1', 'Smith', 'John', 'NSW', '1', '', 'Prospect', '--', '', 'John Smith', '', '0', '0', '--', '', '', '', '', '', '', '', '--', '', '', '', '--', '', '', '', '--', '', '', '', '--', '', '0000-00-00', '', '', '', '', '', '', '', '$15,000', '100%', '15,000', '2015-10-15'); Query to get some data SELECT d.firstname, d.lastname, d.state, d.active, d.prospect_id, d.dlr_group, d.income, d.probability, d.added, n.comment AS LastOfcomment FROM database_prospect_notes AS n LEFT JOIN database_prospects AS d ON n.prospect_id= d.prospect_id WHERE d.active=1 ORDER BY n.note_id DESC LIMIT 1 Edited October 16, 2015 by benanamen Quote Link to comment Share on other sites More sharing options...
WAMFT1 Posted October 16, 2015 Author Share Posted October 16, 2015 Excellent, this partially working, are you able to assist a little further? The above pulls through the last note but I need it to pull the last note for each prospect_id in database_prospects. Effectively all records from database_prospects and the last record for each from database_prospect_notes. Hopefully this makes sense? Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted October 16, 2015 Share Posted October 16, 2015 see this link - http://dev.mysql.com/doc/refman/5.6/en/example-maximum-column-group-row.html where what you are doing is getting the maximum note id per group. using the last method shown at that link (left join) the following works (cannot vouch for how efficient it is, but it is fairly easy to understand or modify to use any JOINed table query for the two sub-queries) - SELECT t1.* FROM (SELECT p.*, n.note_id, n.comment FROM prospects p LEFT JOIN notes n ON p.id = n.prospect_id) t1 LEFT JOIN (SELECT p.id, n.note_id FROM prospects p LEFT JOIN notes n ON p.id = n.prospect_id) t2 ON t1.id = t2.id AND t1.note_id < t2.note_id WHERE t2.id IS NULL; this is using simplified table naming with - a `prospects` table with at least an `id` column and a `notes` table with at least `note_id`, `prospect_id`, and `comment` columns. Quote Link to comment Share on other sites More sharing options...
WAMFT1 Posted October 16, 2015 Author Share Posted October 16, 2015 I am dumb as here... I am completely lost. I thought that this would be a simple exercise. I am back to square 1 now with nothing coming up at all. I don't understand what is going on. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted October 16, 2015 Share Posted October 16, 2015 a) are you running the query directly against your database using a query browser of some kind so that you would be seeing any errors from the query? b) what i posted isn't a copy/paste query. you need to adapt it to use your table and column names. you would also need to add in the active=1 logic. Quote Link to comment Share on other sites More sharing options...
Barand Posted October 16, 2015 Share Posted October 16, 2015 The above pulls through the last note but I need it to pull the last note for each prospect_id in database_prospects. In that case, the test data you provided, with a single prospect, is not fit for purpose Quote Link to comment Share on other sites More sharing options...
WAMFT1 Posted October 16, 2015 Author Share Posted October 16, 2015 If it helps Barand, I have added some more sample data. http://www.wamft.com.au/prospects.sql I am still pulling my hair out here, this is just too complicated for my simple brain to comprehend. I am not a very advance coder. Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted October 16, 2015 Solution Share Posted October 16, 2015 My data mysql> SELECT * FROM database_prospects; +-------------+-----------+----------+--------+ | prospect_id | FirstName | LastName | Active | +-------------+-----------+----------+--------+ | 1 | John | Smith | 1 | | 2 | Jane | Doe | 0 | | 3 | Joe | Bloggs | 1 | | 4 | Davy | Jones | 1 | +-------------+-----------+----------+--------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM database_prospect_notes; +---------+-------------+----------+-----------+---------------------+ | note_id | prospect_id | staff_id | comment | date | +---------+-------------+----------+-----------+---------------------+ | 1 | 1 | 123 | comment 1 | 2015-07-12 00:00:00 | | 2 | 1 | 120 | comment 2 | 2015-09-14 00:00:00 | | 3 | 2 | 125 | comment 3 | 2015-06-22 00:00:00 | | 4 | 2 | 128 | comment 4 | 2015-09-01 00:00:00 | | 5 | 3 | 125 | comment 5 | 2015-08-02 00:00:00 | | 6 | 3 | 129 | comment 6 | 2015-09-05 00:00:00 | +---------+-------------+----------+-----------+---------------------+ 6 rows in set (0.00 sec) Query SELECT p.prospect_id , p.FirstName , p.LastName , notes.staff_id , notes.comment , notes.date FROM database_prospects p LEFT join ( -- -- subquery to get notes record with latest date -- SELECT n.prospect_id , n.staff_id , n.date , n.comment FROM database_prospect_notes n INNER JOIN ( -- -- subquery to find latest dates -- SELECT prospect_id , MAX(date) as date FROM database_prospect_notes GROUP BY prospect_id ) latest ON n.prospect_id = latest.prospect_id AND n.date = latest.date ) notes USING (prospect_id) WHERE p.active = 1; Results +-------------+-----------+----------+----------+-----------+---------------------+ | prospect_id | FirstName | LastName | staff_id | comment | date | +-------------+-----------+----------+----------+-----------+---------------------+ | 1 | John | Smith | 120 | comment 2 | 2015-09-14 00:00:00 | | 3 | Joe | Bloggs | 129 | comment 6 | 2015-09-05 00:00:00 | | 4 | Davy | Jones | NULL | NULL | NULL | +-------------+-----------+----------+----------+-----------+---------------------+ 2 Quote Link to comment Share on other sites More sharing options...
WAMFT1 Posted October 16, 2015 Author Share Posted October 16, 2015 Barand, You are awesome. Works exactly how I need it to. How much do I owe you. I have spent 3 days trying to work this out. Quote Link to comment Share on other sites More sharing options...
Barand Posted October 16, 2015 Share Posted October 16, 2015 Are you prepared to remortgage your house? 1 Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 17, 2015 Share Posted October 17, 2015 (edited) Sub-query's can take a toll on your database. One thing to point out about @Barands solution is that since it is based on datetime rather than the actual last record inserted per prospect, if there are two records with the same exact date-time for a given prospect, you are going to get more than one result for that prospect. Per your specs, you just wanted one result per prospect only and that being the LAST record. Here is an example how to do it that will not take a sub-query toll on the DB and will give you the actual last record inserted per prospect and is a lot less code. * I didn't see that you were trying to get the staff_id or the comment date so I left it out. SELECT p.*, Substring_index(Group_concat(n.`comment` ORDER BY n.note_id DESC), ',', 1 ) AS last_comment FROM database_prospects AS p INNER JOIN database_prospect_notes AS n ON p.prospect_id = n.prospect_id WHERE p.active = 1 GROUP BY p.prospect_id Edited October 17, 2015 by benanamen Quote Link to comment Share on other sites More sharing options...
Barand Posted October 17, 2015 Share Posted October 17, 2015 (edited) It's actually a datetime field in the original database CREATE TABLE IF NOT EXISTS `DATABASE_Prospect_Notes` ( `note_id` int(11) NOT NULL AUTO_INCREMENT, `prospect_id` int(11) NOT NULL, `staff_id` int(11) NOT NULL, `comment` mediumtext NOT NULL, `date` datetime NOT NULL, PRIMARY KEY (`note_id`) ) [edit] PS. Using GROUP_CONCAT() can result in silent truncation when the result is > 1024 characters. As these are TEXT fields the last one may not be complete in the list. If I take out the requirement to show those with no comments (original used right join) then the query shrinks SELECT p.prospect_id , p.FirstName , p.LastName , n.staff_id , n.comment , n.date FROM database_prospects p INNER join database_prospect_notes n ON p.prospect_id = n.prospect_id INNER JOIN ( SELECT prospect_id , MAX(date) as date FROM database_prospect_notes GROUP BY prospect_id ) latest ON n.prospect_id = latest.prospect_id AND n.date = latest.date WHERE p.active = 1; Edited October 17, 2015 by Barand Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 17, 2015 Share Posted October 17, 2015 (edited) Yes barand, the default setting is 1024 and it will truncate at that point. But, it is a setting and it can be changed. Much like the default upload size in PHP is two meg but you usually want to always change that. Just a part of performance tuning. I don't expect newbies to know about it let alone how do it. I am sure you would agree to avoid sub queries when possible. A bit confused why you point out about the original database having a datetime column. The db that you and I both used also has a datetime. The only difference is I didnt use date time to find the last record which as I pointed out will not work if there are two records with the same date time for a given prospect. Edited October 17, 2015 by benanamen Quote Link to comment Share on other sites More sharing options...
Barand Posted October 17, 2015 Share Posted October 17, 2015 I would agree to avoid dependent subqueries. As for ids, I take the view that, for the most part, they are internal linking fields and that an application should work equally well if unique ids were allocated randomly or sequentially. Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 17, 2015 Share Posted October 17, 2015 Ok, confused again. I agree with the unique id's, but we were not talking about unique id's. (Where we?) Quote Link to comment Share on other sites More sharing options...
Barand Posted October 17, 2015 Share Posted October 17, 2015 If we weren't, then what was to stop two comments having the same id? Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 17, 2015 Share Posted October 17, 2015 Ok, really confused now. There are unique id's, the note_id. The page I am on is where I asked you about mentioning the date-time and you started talking about unique ids. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.