Hot answers tagged

8

A LEFT JOIN can solve it, like Laurenz provided. But I suggest an ARRAY constructor in a LATERAL subquery instead: SELECT l.name, b.book_list FROM library l CROSS JOIN LATERAL ( SELECT ARRAY( SELECT title FROM books WHERE library = l.id ) ) b(book_list) WHERE l.id = :library_no; This way, you don't need to aggregate in ...


5

You need a left join: ... FROM library LEFT JOIN books ON library.id = books.library Then you will also get libraries with no books.


1

The values for id 5 and LIMIT must be calculate beforehand and passed on to the query, because LIMIT must have fixed values or you have to use a prepared statement CREATE TABLE table1 (`id` int, `level` int, `score` int, `timestamp` varchar(19)) ; INSERT INTO table1 (`id`, `level`, `score`, `timestamp`) VALUES (4, 1, 70, '2021-01-14 21:50:...


1

In general you should include the scripts that generate your Table schemas and populates them with data. (Also for performance questions, you should include the execution plan too.) I couldn't do a lot of testing without real data, but I believe this query should improve the performance you're seeing: DECLARE @my DATE = '2019-12-01' DECLARE @nextMonthYear ...


1

The planner doesn't understand how the restriction on s.subscription_id interacts with the merge join. The index scan on "s" gets to quickly skip about half the tuples by jumping to the middle of the index, while the index scan on "si" then will read through all the tuples that correspond to the quickly-skipped ones from table "s&...


1

You need to group by the submission_id: like: SELECT submission_id sub_id, GROUP_CONCAT(DISTINCT c1.option_name)datimkt FROM form_results d JOIN field_options c1 ON FIND_IN_SET(c1.option_value, d.datimkt) GROUP BY sub_id;


Only top voted, non community-wiki answers of a minimum length are eligible