I have a database table that looks like this
apiRequest table:
+-------+-------------+--------------------------------------+----------------+
| id | type | userId | device |
+-------+-------------+--------------------------------------+----------------+
| 26786 | healthcheck | 7B0BEA35-7D09-4D46-99C0-45EFF7D3F6E8 | NULL |
| 26788 | root | 7B0BEA35-7D09-4D46-99C0-45EFF7D3F6E8 | iPhone 6S Plus |
| 26789 | healthcheck | 7B0BEA35-7D09-4D46-99C0-45EFF7D3F6E8 | NULL |
| 26791 | root | 7B0BEA35-7D09-4D46-99C0-45EFF7D3F6E8 | iPhone 6S Plus |
| 26792 | healthcheck | 7B0BEA35-7D09-4D46-99C0-45EFF7D3F6E8 | NULL |
| 26794 | healthcheck | 7B0BEA35-7D09-4D46-99C0-45EFF7D3F6E8 | NULL |
| 26795 | root | 054D45BF-8C0D-40D9-9917-12C86C9A1C09 | iPhone 5 |
| 26796 | root | 054D45BF-8C0D-40D9-9917-12C86C9A1C09 | iPhone 5 |
On all the requests that are of type = 'root' a value exists in the device field, this is not the case when type = 'healthcheck'. All entries in this table populate the field userId. The userId field does not have a unique constraint.
I am running a query to get all the healthchecks that match certain conditions, I want to also return the device when I get these healthchecks, and to do that I need to match the userId from the healthcheck entries to the userId from the root entries.
Below is a working sql query, though it takes a very long time to run and is therefore not usable:
SELECT a.id, a.type, a.userId, b.device
FROM apiRequest a
INNER JOIN apiRequest b ON b.userId = a.userId
WHERE a.type = 'healthcheck'
AND b.id = ( SELECT max(id) FROM apiRequest c WHERE c.userId = a.userId AND c.type='root' );
It returns the desired result:
| 26749 | healthcheck | 054D45BF-8C0D-40D9-9917-12C86C9A1C09 | iPhone 5 |
| 26750 | healthcheck | 054D45BF-8C0D-40D9-9917-12C86C9A1C09 | iPhone 5 |
| 26752 | healthcheck | 054D45BF-8C0D-40D9-9917-12C86C9A1C09 | iPhone 5 |
| 26755 | healthcheck | 7B0BEA35-7D09-4D46-99C0-45EFF7D3F6E8 | iPhone 6S Plus |
| 26758 | healthcheck | 7B0BEA35-7D09-4D46-99C0-45EFF7D3F6E8 | iPhone 6S Plus |
| 26762 | healthcheck | 7B0BEA35-7D09-4D46-99C0-45EFF7D3F6E8 | iPhone 6S Plus |
I have tried running a LEFT JOIN on userId but because there are many matches in the right table ( of type root ) for each entry in the left table ( of type healthcheck ) that is not returning the result I need.
MY GOAL:
Is to find the best performing query to select all the entries of type healthcheck and include the matching device from the entries that are of type root.