"inner query"' only option for a left join that needs to match data from two tables?

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

"inner query"' only option for a left join that needs to match data from two tables?

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:

code:

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':

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)
entries (nameid,titleid,value)
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...