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 ...
answered Jan 14 at 5:10
Erwin Brandstetter
127k1616 gold badges291291 silver badges432432 bronze badges
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
Related Tags
join × 1687mysql × 637
sql-server × 319
postgresql × 313
performance × 190
oracle × 110
subquery × 95
select × 93
query-performance × 84
query × 82
group-by × 82
optimization × 78
count × 61
index × 56
t-sql × 54
update × 49
aggregate × 43
union × 42
database-design × 37
mariadb × 35
view × 32
foreign-key × 30
execution-plan × 28
sql-server-2008 × 25
innodb × 24