"inner query"' only option for a left join that needs to match data from two tables?
lets assume I got three tables:
users (ID, name)
fields (ID, title)
I want an record back for every (users * fields) (with the corresponding value in 'entries' table)... So if 10 users, 10 fields, id expect 100 results back, each result with their corresponding 'value' in entries if any (null if none). but the curve ball: there may not be a record in the 'entries' table (so if a new field is added, every single user would not have that field in entries yet).
This wont work:
select mythings from users, fields left join entries on entries.titleID = fields.id /* this would not account for the userID matched to users.ID */
My effective problem is since there might not be a record in 'entries' I need to do a left join with entries, but id need data from both users and fields tables. This is my 'solution':
select * from
(select mythings from users, fields) as T
left join entries ON T.ID = entries.nameid and T.ID = entries.titleID
Is there a better way to do this? Or is this the accepted solution to this?