www.webdeveloper.com
Results 1 to 6 of 6

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

Hybrid View

  1. #1
    Join Date
    Nov 2010
    Posts
    67

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

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

  2. #2
    Join Date
    Nov 2010
    Posts
    67
    bump? This isnt working that great...

    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:
    Code:
    select * from (
    	select clients.ID as 'userID',
    	    clients.name,
    	    fields.ID as 'PKID',
    	    fields.title,
    	    fields.type,
    	    fields.archived,
    		clients.RET
    	from fields, clients
    	where fields.archived = 0
    	$clientNameFilter
    	order by $sort, clients.ID, fields.ID asc
    	) as tt left joinclientextra on (tt.userID = clientExtra.clientID and tt.PKID = clientExtra.fieldID)

  3. #3
    Will this work

  4. #4
    Join Date
    Nov 2010
    Posts
    67
    ? your post is empty...

    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.

  5. #5
    Join Date
    Sep 2011
    Posts
    59
    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...

  6. #6
    Join Date
    Nov 2010
    Posts
    67
    my tables arent linked with constraints, but here is the schema

    schema.jpg

    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles