How to left join with first matching row and fill the rest with null?-CodePudding

My core case a little bit more complicated, so I will illustrate it with an example. Let's pretend I have tables like these:

animals

name (PK)	color
cat1	white
cat2	red
dog1	black

places

place (PK)	name (FK)	amount
cage1	cat1	2
room1	cat1	3
cage2	dog1	5

in_sale

name (FK)	amount	price
cat1	1	50.00
dog1	3	600.00
cat2	2	1.00

Here's code to create them:


    CREATE TABLE `animals` (
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `color` varchar(100) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `in_sale` (
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `amount` int(11) NOT NULL,
  `price` varchar(100) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `places` (
  `place` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `amount` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `animals` (`name`, `color`) VALUES
('cat1', 'white'),
('cat2', 'red'),
('dog1', 'black');

INSERT INTO `in_sale` (`name`, `amount`, `price`) VALUES
('cat1', 1, '25.00'),
('cat1', 1, '50.00'),
('cat2', 2, '1.00'),
('dog1', 3, '600.00');

INSERT INTO `places` (`place`, `name`, `amount`) VALUES
('cage1', 'cat1', 2),
('cage2', 'dog1', 5),
('room1', 'cat1', 3);

Now I wanted to run a query:

SELECT a.*, p.place, p.amount AS amount_in_place, s.sales 
FROM animals AS a 
LEFT JOIN places AS p ON a.name=p.name 
LEFT JOIN (SELECT GROUP_CONCAT("Amount: ",amount, " and price: ",price separator ", ") AS sales, name FROM in_sale GROUP BY name) AS s ON s.name=a.name 
ORDER BY a.name;

But unfortunately, I realized that result it's not something that I expected.

RESULT:

name	color	place	amount_in_place	sales
cat1	white	cage1	2	Amount: 1 and price: 25.00, Amount: 1 and price: 5...
cat1	white	room1	3	Amount: 1 and price: 25.00, Amount: 1 and price: 50.00
cat2	red	NULL	NULL	Amount: 2 and price: 1.00
dog1	black	cage2	5	Amount: 3 and price: 600.00

EXPECTED:

name	color	place	amount_in_place	sales
cat1	white	cage1	2	Amount: 1 and price: 25.00, Amount: 1 and price: 50.00
cat1	white	room1	3	NULL
cat2	red	NULL	NULL	Amount: 2 and price: 1.00
dog1	black	cage2	5	Amount: 3 and price: 600.00

What can I change in my query to join the last table with just first matching row? I tried to manipulate a little bit with LIMIT 1,OUTER JOIN and MIN as I found some suggestions in similar questions, but I couldn't achieve my goal anyway.

IMPORTANT! Pay attention that animals can be in sale, even if they're have no place assigned.

CodePudding user response：

Thanks to @Akina I can provide the final version of code for my example:

SELECT name,
   animals.color,
   places.place,
   places.amount amount_in_place,
   CASE WHEN name = LAG(name) OVER (PARTITION BY name ORDER BY place)
   THEN 
      null
   ELSE 
      (SELECT GROUP_CONCAT("Amount: ",amount, " and price: ",price 
      SEPARATOR ", ") AS sales 
      FROM in_sale 
      WHERE in_sale.name=animals.name GROUP BY name) 
   END sales
FROM animals
LEFT JOIN places USING (name)
LEFT JOIN in_sale USING (name)
GROUP BY 1,2,3,4;

Note that it works only for MySQL version 8 or higher.