i have mysql table contains column called sequence.
here statment:
select a.modelyear, count(*) yearcount styles a, jpgs b, models c, divisions d d.divisionid = c.divisionid , a.styleid = b.styleid , c.modelid = a.modelid , a.mktclassid in ($this->mktclassids) , a.sequence = 0 $this->where group a.modelyear; the above query should output counts equal query:
select count(distinct(modelid)) styles mktclassid in ($this->mktclassids) $this->where my problem in order every unique modelid count. sequence can equal 0 or 1, not both. modelid might have sequence = 0. if doesnt equal 0 equal 1. need if statment in clause.
this think need:
if modelid has a.sequence = 0, use a.sequence = 0 else modelid not have a.sequence = 0, use a.sequence = 1 i not mysql guy sorry if not expressing need.. if modelid not have sequence = 0 have sequence = 1
the styles table has many trim levels each model. , first trim either equal 0 or 1. example of styles table
styleid modelid sequence 350422 25156 1 350425 25156 4 350426 25156 5 350427 25156 7 350428 25156 11 350429 25156 6 350432 25156 9 350433 25156 10 356717 25156 8 styleid modelid sequence 308367 18875 0 308368 18875 1 308369 18875 2 308370 18875 3 308371 18875 4 i trying distinst modelid table. not believe can use distinct though because effect entire query.
so can see model not have sequence = 0, need use sequence = 1 maybe contains 1 or 0...i not sure appreciated thanks
have tried
... , (a.sequence=0 or a.sequence=1) ... i joins instead of using statements optimize query.
select a.modelyear, count(*) yearcount styles inner join jpgs b on (a.styleid = b.styleid) inner join models c on (c.modelid = a.modelid) inner join divisions d on (d.divisionid = c.divisionid) a.mktclassid in ($this->mktclassids) , (a.sequence=0 or a.sequence=1) $this->where group a.modelyear; the above query items sequences 1 or 0 next query took me thinking. explain query comments #, should items not have both sequence of 1 , 0:
select a.modelyear, count(*) yearcount styles inner join jpgs b on (a.styleid = b.styleid) inner join models c on (c.modelid = a.modelid) inner join divisions d on (d.divisionid = c.divisionid) a.mktclassid in ($this->mktclassids) #above same previous #and modelid in modelids either having sequence 0 or 1 not both: , a.modelid in ( #select modelids have either sequence 0 or 1 not both select styles.modelid styles ( styles.modelid in (#modelids having sequence 0 not 1 select styles.modelid styles styles.sequence=0 group styles.modelid ) , styles.modelid not in ( select styles.modelid styles styles.sequence=1 group styles.modelid ) ) or ( styles.modelid not in (#modelids having sequence 1 not 0 select styles.modelid styles styles.sequence=0 group styles.modelid ) , styles.modelid in ( select styles.modelid styles styles.sequence=1 group styles.modelid ) ) ) $this->where group a.modelyear i hope not confusing
user states query runs forever. try subquery part , let me know performance.
select styles.modelid styles ( styles.modelid in ( select styles.modelid styles styles.sequence=0 group styles.modelid ) , styles.modelid not in ( select styles.modelid styles styles.sequence=1 group styles.modelid ) ) or ( styles.modelid not in ( select styles.modelid styles styles.sequence=0 group styles.modelid ) , styles.modelid in ( select styles.modelid styles styles.sequence=1 group styles.modelid ) ) so looking distinct modelids table, prefered sequence 0 or 1 - op
select modelid, sequence ( select modelid, sequence styles order sequence asc ) sub_query group modelid thats need. how add clause original query? - op
select a.modelyear, count(*) yearcount styles a, jpgs b, models c, divisions d d.divisionid = c.divisionid , a.styleid = b.styleid , c.modelid = a.modelid , a.mktclassid in ($this->mktclassids) , a.modelid in ( select modelid ( select modelid, sequence styles order sequence asc ) sub_query group modelid ) $this->where group a.modelyear; which result same as:
select a.modelyear, count(*) yearcount styles a, jpgs b, models c, divisions d d.divisionid = c.divisionid , a.styleid = b.styleid , c.modelid = a.modelid , a.mktclassid in ($this->mktclassids) , a.modelid in ( select modelid styles group modelid ) $this->where group a.modelyear; i not think acutally want though. not need clause need have better query altogether. other tables necessary? does $this->where equate to?
after thinking on hour, think might want. limits styles table 1 modelid , sorts them lowest sequence , joins on other tables , applies mktclassid in clause , $this->where clause:
select a.modelyear, count(*) yearcount ( select * ( select * styles order sequence asc ) subquery group modelid ) inner join jpgs b on (a.styleid = b.styleid) inner join models c on (c.modelid = a.modelid) inner join divisions d on (d.divisionid = c.divisionid) a.mktclassid in ($this->mktclassids) , $this->where group a.modelyear; after this, done , have no more advice give other clarify question , please provide sqlfiddle , give more examples of expected output. problem facing because trying going things wrong way. if possible give reason why doing other here's problem... others may able simplify process , provide solution solves real problem. luck!
Comments
Post a Comment