0

the following is the situation. I need to connect an order-table with a message-table. But i'm only interested in the first message(lowest message-id). The connection between the tables is the orderid.

        $result = $this->db->executeS('
        SELECT o.*, c.iso_code AS currency, s.name AS shippingMethod, m.message AS note
        FROM '._DB_PREFIX_.'orders o
        LEFT JOIN '._DB_PREFIX_.'currency c ON c.id_currency = o.id_currency
        LEFT JOIN '._DB_PREFIX_.'message m ON m.id_order = o.id_order
        LEFT JOIN '._DB_PREFIX_.'carrier s ON s.id_carrier = o.id_carrier
        LEFT JOIN jtl_connector_link l ON o.id_order = l.endpointId AND l.type = 4
        WHERE l.hostId IS NULL AND o.date_add BETWEEN DATE_SUB(NOW(), INTERVAL 1 WEEK) AND NOW()
        GROUP BY o.id_order
        HAVING MIN(m.id_message) 
        LIMIT '.$limit
    );

This query works so far. But now orders without a message are missing.

Thank you for your help! Markus

myfire
  • 33
  • 6
  • You are not doing any comparison in `HAVING` so better to remove it – Darshan Mehta Oct 06 '17 at 12:49
  • Thank you. I think I misunderstood HAVING min(m.id_message) as "just return the rows with the smallest messageid". – myfire Oct 06 '17 at 12:56
  • Skip the GROUP BY and HAVING clauses, do `ORDER BY m.id_message LIMIT 1` instead. – jarlh Oct 06 '17 at 13:02
  • 1
    That GROUP BY is invalid, it won't execute on newer MySQL versions (unless in compatibility mode), may return unpredictable results with older MySQL versions. The general GROUP BY rule says: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function! – jarlh Oct 06 '17 at 13:05
  • 1
    This is the most frequently askes question under this tag. Nevertheless, if you're still struggling, see: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Oct 06 '17 at 13:21
  • Why all the outer joins? Are there orders without a currency or carrier? – Thorsten Kettner Oct 06 '17 at 13:34
  • Thank you for the answers. I learned a lot here! – myfire Oct 06 '17 at 14:13
  • Possible duplicate of [Error related to only\_full\_group\_by when executing a query in MySql](https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql) – philipxy Oct 21 '17 at 01:48

1 Answers1

0

You want to select several orders and per order the first message. This is generally difficult in MySQL for the lack of window functions (e.g. ROW_NUMBER OVER). But as it's just one column from the message table you are interested in, you can use a subquery in the SELECT clause.

SELECT 
  o.*, 
  c.iso_code AS currency, 
  s.name AS shippingMethod, 
  (
    SELECT m.message 
    FROM message m 
    WHERE m.id_order = o.id_order
    ORDER BY m.id_message
    LIMIT 1
  ) AS note
FROM orders o
JOIN currency c ON c.id_currency = o.id_currency
JOIN carrier s ON s.id_carrier = o.id_carrier
WHERE o.date_add BETWEEN DATE_SUB(NOW(), INTERVAL 1 WEEK) AND NOW()
AND NOT EXISTS
(
  SELECT *
  FROM jtl_connector_link l 
  WHERE l.endpointId = o.id_order
  AND l.type = 4
);
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73