How do I optimize MariaDB query with subqueries in FROM clause?

Question

imagine these two tables.

Table A

ID col1 col2 col3
1  foo  baz  bar
2  ofo  zba  rba
3  oof  abz  abr

Table B

A_ID field_name field_value
1    first      Jon
1    last       Doe
2    first      Adam
2    last       Smith

etc..

Now I would like to have a query (current one looks like this)

SELECT 
    a.id, 
    a.col1, 
    a.col2, 
    (SELECT field_value FROM B WHERE A_ID = a.id AND field_name = 'first') as first_name, 
    (SELECT field_value FROM B WHERE A_ID = a.id AND field_name = 'last') as last_name
FROM A a
WHERE (SELECT COUNT(*) FROM B WHERE A_ID = a.id) = 2;

This query is working. What I would like to achieve would be something like this.

SELECT 
  a.id,
  a.col1,
  a.col2,
  (SELECT field_value FROM b WHERE b.field_name = 'first') as first_name,
  (SELECT field_value FROM b WHERE b.field_name = 'last') as last_name
FROM
  A a,
  (SELECT field_value, field_name FROM B WHERE A_ID = a.id) b
WHERE (SELECT COUNT(*) FROM b) = 2;

How would my approach look correctly? Is there any other way to get rid of the multiple queries of the table B?

Thank you!

Answer 1

I would replace your correlated subqueries with joins:

SELECT 
    a.id, 
    a.col1, 
    a.col2,
    b1.field_value AS fv1,
    b2.field_value AS fv2
FROM A a
LEFT JOIN B b1
    ON a.id = b1.A_ID AND b1.field_name = 'first'
LEFT JOIN B b2
    ON a.id = b2.A_ID AND b2.field_name = 'last';

This answer assumes that a left join from a given A record would only match at most one record in the B table, which, however, is a requirement anyway for your correlated subqueries to only return a single value.