arianhojat Posted September 12, 2006 Share Posted September 12, 2006 Hello I was making a Query earlier and it seemed basically I was repeating myself and being inefficient so i was wondering if anyone ran into the same issues and has a solution...The Query looks long like...SELECT *FROM111join 222join 333join 444join 555join 666join 777WHERELLLLLOOOONNNNGGGGLLLLLOOOONNNNGGGGLLLLLOOOONNNNGGGGmy Query returns something likeIndivClassID(primary key) MasterClassID ... ... ...100 66 ... ... ...101 66 ... ... ...201 76 ... ... ...202 88 ... ... ...202 88 ... ... ...202 88 ... ... ...I would like to return Count(MasterClassID) in that Query, so should look like...100 66 2 ... ... ...101 66 2 ... ... ...201 76 1 ... ... ...202 88 3 ... ... ...202 88 3 ... ... ...202 88 3 ... ... ...So my 1st thought is usual Subquery. but it seems very ghetto/repetitive to do this... If I do a GROUP BY MasterClassID, of course, I lose info so i guess I can't do that. The ghetto way i came up with which works, but probably uses double the time is...SELECT *, ( SELECT Count(MasterClassID) FROM 111_temp join 222_temp join 333_temp join 444_temp join 555_temp join 666_temp join 777_temp WHERE LLLLL OOOO NNNN GGGG LLLLL OOOO NNNN GGGG LLLLL OOOO NNNN GGGG AND 111_temp.MasterClassID=111.MasterClassID /* returns count for this 1 row */ GROUP BY MasterClassID) as CountFROM111join 222join 333join 444join 555join 666join 777WHERELLLLLOOOONNNNGGGGLLLLLOOOONNNNGGGGLLLLLOOOONNNNGGGG Quote Link to comment Share on other sites More sharing options...
fenway Posted September 12, 2006 Share Posted September 12, 2006 Well, depending on what you're using it for, you have a few options. You can issue a second query and just get back the counts. You can forgoe the UIDs of the things your grouping, if you don't need them, or GROUP_CONCAT(), or whatever. You can run a correlated subquery on whatever table contains MasterClassID and do a much simpler count, probably. Quote Link to comment Share on other sites More sharing options...
arianhojat Posted September 12, 2006 Author Share Posted September 12, 2006 only thing about GROUP_CONCAT is i would get result like...'100,101' 66 2'201' 76 1'202,202,202' 88 3so it would have the string of the Individual ID's which are related to their respected master ID, and then the count of the masterIDs for that group.but the other data in 'Individual ' table is lost since it is GROUPED BY MasterClassID.i guess ill stick with longgg subselect or a 2nd query in the php loop.antother option maybe is...4. Use a php database cursor (aka use mysql_data_seek function) to keep looking into the future, incrementing a count variable until new MasterClassID reached, then put cursor back at row 'i' that it is processing and it will have the count information. eh as well but maybe 7-10 lines of extra code. but then again the subselect is the same... any other suggestions, i am open to.thanks, fenway. Quote Link to comment Share on other sites More sharing options...
fenway Posted September 12, 2006 Share Posted September 12, 2006 Not that you couldn't count them yourself in PHP... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.