Mysql – Query finds no values when sharppend

greatest-n-per-groupMySQLquerysubquery

I am trying to join two datasets and find the most recent certification date

I the following 2 datasets

Certifications

(1, 'Judge Level 1', 'Judge', 10, 2),
(2, 'Judge Level 2', 'Judge', 20, 2),
(3, 'Judge Level 3', 'judge', 30, 2),
(4, 'Olympic Judge', 'olympic judge', 30, 2),
(5, 'Junior Leader', 'leadership', 10, 1),
(6, 'Trainee', 'leadership', 10, 1),
(7, 'Instructor', 'leadership', 10, 1);

members_certifications

(1000052, 7, '2016-07-01'),
(1000053, 1, '2016-07-01'),
(1000053, 3, '2018-07-01'),
(1000053, 4, '2019-07-01'),
(1000053, 5, '2020-07-01'),
(1000059, 1, '2016-07-01'),
(1000059, 2, '2017-07-01'),
(1000059, 5, '2016-07-04'),
(1000059, 6, '2016-07-01'),
(1000059, 6, '2016-07-02');

I would like the result set to be

1000059, Junior Leader , leadership, 10, 2016-09-04
1000059, Judge,Judge Level 2, 20, 2016-09-04

I created a exact example http://sqlfiddle.com/#!9/20c30b1/3/1 MySql version 5.6

The first query produces a large data set when I reduce the query by setting a specific id (in this case 100059) I get 0 results.

Also is there a more query efficient or less bulky way create this query.

CREATE TABLE `certifications` (
  `id` int(11) NOT NULL,
  `title` varchar(254) NOT NULL,
  `type` varchar(254) NOT NULL,
  `sort_order` int(11) NOT NULL,
  `expiration_interval` tinyint(4) NOT NULL DEFAULT '2'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `members_certifications` (
  `member_id` int(11) NOT NULL,
  `certification_id` int(11) NOT NULL,
  `effective_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query 1

SELECT members_certifications.member_id AS member_id,certifications.title AS title,
certifications.type AS type,certifications.sort_order AS sort_order,
MaxTable.effective_date AS effective_date FROM members_certifications, certifications,
(SELECT     members_certifications.member_id AS member_id,     certifications.type AS type,
 MAX(members_certifications.effective_date) AS effective_date   
 FROM     members_certifications, certifications  
 WHERE     members_certifications.certification_id = certifications.id   
 GROUP BY      certifications.type,members_certifications.member_id   ) MaxTable 
 WHERE  members_certifications.certification_id = certifications.id
 AND certifications.type = MaxTable.type  AND members_certifications.member_id = MaxTable.member_id  AND members_certifications.effective_date = MaxTable.effective_date 
ORDER BY  MaxTable.member_id, certifications.sort_order, MaxTable.type, certifications.title;

query II (0 results)

SELECT members_certifications.member_id AS member_id,certifications.title AS title,certifications.type AS type,certifications.sort_order AS sort_order,MaxTable.effective_date AS effective_date FROM members_certifications, certifications,  (SELECT     members_certifications.member_id AS member_id,     certifications.type AS type,     MAX(members_certifications.effective_date) AS effective_date   FROM     members_certifications, certifications   WHERE     members_certifications.certification_id = certifications.id   GROUP BY      certifications.type,members_certifications.member_id   ) MaxTable WHERE  members_certifications.certification_id = certifications.id  AND members_certifications.member_id = 100059  AND certifications.type = MaxTable.type  AND members_certifications.member_id = MaxTable.member_id  AND members_certifications.effective_date = MaxTable.effective_date ORDER BY  MaxTable.member_id, certifications.sort_order, MaxTable.type, certifications.title ;

Best Answer

I'm not exactly sure what you are asking, but I would suggest that you use aliases and ANSI joins for your queries:

SELECT mc1.member_id AS member_id
      ,c1.title AS title
      ,c1.type AS type
      ,c1.sort_order AS sort_order
      ,MaxTable.effective_date AS effective_date 
FROM members_certifications mc1
JOIN certifications c1
    ON mc1.certification_id = c1.id
JOIN (SELECT mc2.member_id AS member_id
           , c2.type AS type
           , MAX(mc2.effective_date) AS effective_date   
      FROM members_certifications mc2
         , certifications c2 
      WHERE mc2.certification_id = c2.id   
      GROUP BY c2.type, mc2.member_id   
     ) MaxTable 
    ON c1.type = MaxTable.type  
   AND mc1.member_id = MaxTable.member_id  
   AND mc1.effective_date = MaxTable.effective_date 
ORDER BY  MaxTable.member_id, c1.sort_order, MaxTable.type, c1.title;

Regarding your second query in the fiddle, there is a slight difference between:

1000059 and 100059

So if you change that to:

SELECT mc1.member_id AS member_id
      ,c1.title AS title
      ,c1.type AS type
      ,c1.sort_order AS sort_order
      ,MaxTable.effective_date AS effective_date 
FROM members_certifications mc1
JOIN certifications c1
    ON mc1.certification_id = c1.id
JOIN (SELECT mc2.member_id AS member_id
           , c2.type AS type
           , MAX(mc2.effective_date) AS effective_date   
      FROM members_certifications mc2
         , certifications c2 
      WHERE mc2.certification_id = c2.id   
      GROUP BY c2.type, mc2.member_id   
     ) MaxTable 
    ON c1.type = MaxTable.type  
   AND mc1.member_id = MaxTable.member_id  
   AND mc1.effective_date = MaxTable.effective_date 
WHERE mc1.member_id = 1000059   
ORDER BY  MaxTable.member_id, c1.sort_order, MaxTable.type, c1.title;

does that give you the expected answer?

MySQL 8+ supports window functions which are far more efficient. Instead of doing a join against a sub-select you can use a window function to determine max_effective_date, and then filter with that:

SELECT member_id, title, type, sort_order, effective_date
FROM (
    SELECT mc1.member_id AS member_id
          ,c1.title AS title
          ,c1.type AS type
          ,c1.sort_order AS sort_order
          ,mc1.effective_date  
          ,Max(effective_date) over (partition by member_id, c2.type) AS max_effective_date 
   FROM members_certifications mc1
   JOIN certifications c1
        ON mc1.certification_id = c1.id
) AS T
WHERE max_effective_date = effective_date 
ORDER BY member_id, sort_order, type, title;

Related Solutions

Mysql – Unexplained InnoDB timeouts

I know this is really late, but you really need to capture the output of SHOW ENGINE INNODB STATUS; during that query to see why it's waiting.

If it happens a lot during a specific time, it would be easy to just grab that output every x seconds and hope you capture it (or perhaps artificially generate the load).

Mysql – Getting a Deadlock,

The system is starting to complain about the number of comments.

I'd be interested in seeing the statements as they are sent to the server - i.e. the fully generated SQL statements, i.e. with real values, not ? parameter place holders.

The statement that appears to be causing the issue is this one.

INSERT INTO `inventoryitems` VALUES (DEFAULT, 4, '595', null, 5390001, 5, 1, 1, '', -1, 0, -1, '')

Now, inventory items has a FOREIGN KEY constraint

CONSTRAINT FK_inventoryitems_1 FOREIGN KEY (characterid) REFERENCES characters (id) ON DELETE CASCADE

What is the result of the following query

SELECT COUNT(*) FROM characters WHERE characters.id = 595;

The values here are also of interest.

ps = con.prepareStatement("INSERT INTO `inventoryitems` VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
pse = con.prepareStatement("INSERT INTO `inventoryequipment` VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

[EDIT]

I'm starting to get some insight into what may be going on. You appear to be using some sort of framework for your database access (that's the reason why your SQL has '595' for an integer insert instead of 595.

It's possible that the transaction isolation level is being set by the framework to something other than read committed (see here). Try and ensure that your transactions use read committed - set the transaction level. Try adding this code to yours after the line

Connection con = DatabaseConnection.getConnection(); con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); // Added line.

Also, check out this and the rest of the thread.

[EDIT] in response to "How do i check it? , can you guide me?"

What I meant was for you to add the line above to your code and rerun your system to the point where you got your original error. Do you still get an error? If you do, then is it different to the original error? If it is different, then post the error text here. HTH.

Could you take a dump of your database and put it online? You can anonymise anything sensitive. Also, zip up your Java code and I'll run that here against your schema. My Java's a bit rusty, but running it on a different system might give us some clues! :-)

[EDIT] in response to OP's comment "can you give me an example of indexes [code example] ?".

See here (you can also use the KEY keyword as a synonym for INDEX - see the docco here. You should add to your inventoryitems CREATE TABLE statement lines such as

INDEX `ii_type_ix` (type), 
INDEX `ii_account_id_ix` (accountid),

As for changing your Java code - I'm not 100% sure, but the problem lies with the database so your efforts should be concentrated there. Where you can change the code is in varying the order of your queries (but keeping the original functionality).

Best Answer

Related Solutions

Mysql – Unexplained InnoDB timeouts

Mysql – Getting a Deadlock,

Related Question