Currently I'm busy optimizing a query.
I've got the following query:
SELECT a.*, c.*
FROM a
LEFT JOIN b ON a.id = b.a_id
LEFT JOIN c ON b.c_id = c.id
WHERE x
I'm only interested in 1 row of the table c. I don't care for others..
I can only reach table c via table b.
There are many b tables with an a_id.
For performance reasons I would like to implement this like this.
SELECT a.*, c.*
FROM a
LEFT JOIN b ON a.id = b.a_id LIMIT 1
LEFT JOIN c ON b.c_id = c.id
WHERE x
Why isn't (a form of this contruct) not an sql standard. And hasn't any database implemented this contruct. Or am I missing something?
When you have many indirect joins this can be a huge time saver.
Btw. Alternative syntax for sql server:
SELECT a.*, c.*
FROM a
LEFT JOIN TOP 1 b ON a.id = b.a_id
LEFT JOIN c ON b.c_id = c.id
WHERE x
** btw The database I need this construct for, has got many redundant records.