3

I have an 1:m association.

symbols table has many rows in company_key_statistics table. company_key_statistics has column createdAt which is timestamp indicating when row has been created. I need to join latest symbols with company_key_statistics, but I need only latest company_key_statistics. For example I need to get ORCL and MSFT symbols and only their latest company_key_statistics.

So far I have tried this.

SELECT `symbols`.`id`, 
       `symbols`.`symbol`, 
       `statistics`.
       `marketCapitalization` 
FROM   `symbols` 
       LEFT JOIN (SELECT `s`.`companyId`, 
                         `s`.`marketCapitalization` 
                  FROM   `company_key_statistics` AS `s`
                  WHERE  `s`.`companyId` = `symbols`.`id` 
                  ORDER by `createdAt` 
                  DESC LIMIT 1) AS `statistics` 
ON     `symbols`.`id` = `statistics`.`companyId` 
WHERE  `symbols`.`symbol` IN ('ORCL', 'SNAP');

But unfortunately I found that I can't use columns from parent query in JOIN subqueries.

How can I achieve to this?

2

Try this:

SELECT sym.id, sym.symbol, s.marketCapitalization 
  FROM symbols AS sym 
  INNER JOIN company_key_statistics AS s 
     ON sym.id = s.companyId 
  INNER JOIN (SELECT companyId, MAX(createdAt) AS createdAt 
                FROM company_key_statistics 
                GROUP BY companyId) AS smax 
     ON smax.companyId = s.companyId AND smax.createdAt = s.createdAt 
  WHERE sym.symbol IN ('ORCL','SNAP');

Since your posted example was a LEFT JOIN, maybe you want also companies without market capitalization data in your answer. In this case, we have to nest the subselects, in this way:

SELECT sym.id, sym.symbol, s.marketCapitalization 
  FROM symbols AS sym 
  LEFT JOIN (SELECT * 
               FROM company_key_statistics
               INNER JOIN (SELECT companyId, MAX(createdAt) AS createdAt 
                             FROM company_key_statistics 
                             GROUP BY companyId) AS smax 
               USING (companyId, createdAt)) AS s 
    ON sym.id = s.companyId 
 WHERE sym.symbol IN ('ORCL','SNAP');

The general problem we are addressing here is called groupwise maximum and many SO and DBA.SE answers deal with it. Check this one for instance.

  • Man you are genius! – Aren Hovsepyan Apr 12 '17 at 0:03
  • Also I have learned how this approach is called :) – Aren Hovsepyan Apr 12 '17 at 0:12
  • You don't need nested subqueries. You can use LEFT joins wiht your first query. – ypercubeᵀᴹ Apr 12 '17 at 8:44
3

For a correlated subquery you should use a CROSS APPLY, unfortunately MySql doesn't allow it, but you can simulate it on this way:

SELECT     `symbols`.`id`, 
           `symbols`.`symbol`, 
           `statistics`.
           `marketCapitalization`,
           `s`.`companyId`,
           `s`.`marketCapitalization`  
FROM       `symbols` 
INNER JOIN `company_key_statistics` `s`
ON         `s`.`pk` = (SELECT `pk`          -- the PK of company_key_statistics
                       FROM   `company_key_statistics`
                       WHERE  `companyId` = `symbols`.`id` 
                       ORDER by `createdAt` 
                       DESC LIMIT 1) 
WHERE  `symbols`.`symbol` IN ('ORCL', 'SNAP');

You can find some example on SO.

  • Yes, that’s the way to do it in MySQL. In PostgreSQL you would use JOIN LATERAL. – Dario Apr 11 '17 at 21:23
  • @Dario - thus question is about mysql - what does this have to do with PostgreSQL? – Max Vernon Apr 11 '17 at 21:35
  • 1
    @MaxVernon CROSS APPLY has nothing to do with MySQL either and was mentioned in the answer, without objections. If, for a wider perspective, an answer mentions SQL dialects not strictly relevant to the answer itself, I thought it appropriate to add a comment to widen the perspective even further. – Dario Apr 11 '17 at 21:56
  • Unfortunately, this will not work since, clause ON is same as s.createdAt = symbols.id and I got this result imgur.com/a/OezQd, as you can see here are 2 ORCL rows, but I need only one, which is latest by createdAt – Aren Hovsepyan Apr 11 '17 at 22:01
  • 1
    The ON condition needs to be corrected to use company_key_statistics pk. – ypercubeᵀᴹ Apr 12 '17 at 8:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.