"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?
I got over 400 users and 10 fields. That means I got over 4000 records in 'entries' table. Getting all clients data on a single page takes over 10 seconds. Im sure there got to be a better way.
In the meantime I coded it so it limits to max of 50 users per page and it still takes few seconds (and in this situation 50 users per page is not ideal).
Here is the exact query im using:
select * from (
select clients.ID as 'userID',
fields.ID as 'PKID',
from fields, clients
where fields.archived = 0
order by $sort, clients.ID, fields.ID asc
) as tt left joinclientextra on (tt.userID = clientExtra.clientID and tt.PKID = clientExtra.fieldID)
My fix to make it a max limit of 50 users per page made the query even a tad slower (as it required yet another 'inner query', but at least it takes less then 2s to generate now with a limit of 50 records per page...
These queries are so crazily slow im almost thinking it would be 100x faster (literally) in the end to code a PHP solution where I simple send three seperate query to all three tables then have PHP figure it all out. But that kind of defeats the point of SQL.
how you relate your tables?
users (ID, name)
fields (ID, title)
you tables not related... it is does not make sense....
Of cause if you creating thousands records and transferred them from SQL server to you page it takes time and slow down you page, but if you will get just 50 records from database and display them it will fly...
Every row in the table 'fields' is something that every single client can have set in the table 'clentextra'.
So the table 'clientextra' are entries (where the data is). So assuming the table 'fields' has 10 entries/rows, then each client will have up to 10 entries in table 'clientextra' (I say up to, as its possible some would not be set if they are either optional or a new field was created after the client was added).
So I want a query where id get a record back for (number of client * number of fields) where if a corresponding entry was found in 'clientextra' it would be displayed, else it would give me nothing for that particular row.
In retrospect, another way of going about this: Have one query so I know how many items in table 'fields' then a simple query where the client ID and fields ID is found in the 'clientextra'. From that I could write some PHP logic that would know to generate empty/blank textboxes...
Last edited by ZeratulsDomain; 02-05-2013 at 02:41 PM.
Reason: flying monkeys