How to use combined primary key in multi-table query-CodePudding

DDL & DML examples:

CREATE TABLE a (
  `ID` VARCHAR(8), `yearly_return` DECIMAL(16,2)
);
INSERT INTO a
VALUES
  ('0001', 0.4), ('0002', 0);
CREATE TABLE b (
  `ID` VARCHAR(8), `drawdown` DECIMAL(16,2)
);
INSERT INTO b
VALUES
  ('0001', 0.2), ('0002', 0);
CREATE TABLE c (
  `ID` VARCHAR(8), `author` VARCHAR(255)
);
INSERT INTO c
VALUES
  ('0001', 'join'), ('0002', '');
CREATE TABLE d (
  `ID` VARCHAR(8), `date` VARCHAR(20), `yield_the_day` DECIMAL(16,2)
);
INSERT INTO d
VALUES
  ('0001','20220910', 0.02), ('0001','20220911', 0.06);

mysql: 8.0.26

Background: id (0001) is the primary key in tables a, b, c, and id and date (0001, 20220912) are combined primary keys in table d

Question: How to query abcd, the result set is e. And the latest record in d needs the latest date

Try steps:

SELECT
    a.yearly_return,
    b.drawdown,
    c.author,
    
FROM
    a
    INNER JOIN b test ON a.id = b.id
    INNER JOIN c ON a.id = c.id

CodePudding user response：

You want a lateral join, i.e. join a subquery that refers to the other tables:

SELECT
  a.yearly_return,
  b.drawdown,
  c.author,
  d.yield_the_day    
FROM a
INNER JOIN b ON b.id = a.id
INNER JOIN c ON c.id = a.id
CROSS JOIN LATERAL
(
  SELECT *
  FROM d
  WHERE d.id = a.id
  ORDER BY date DESC
  LIMIT 1
) d
ORDER BY a.id;