join - Need help to make one mysql query to get expected result for my requirement -



join - Need help to make one mysql query to get expected result for my requirement -

i facing few issue write mysql query in scope result. getting appropriate result using existing query not written appropriate way. here query:

select c.id, c.chn_name,c.chn_logo, (select id tv_showtime showtime<='2013-02-18 10:28:35' , status='enable' , chn_id=c.id order id desc limit 0,1) currentshowid, (select tv_showtime tv_showtime showtime<='2013-02-18 10:28:35' , status='enable' , chn_id=c.id order id desc limit 0,1) currentshowtime , (select tv_showtime tv_showtime showtime >'2013-02-18 10:28:35' , status='enable' , chn_id=c.id order id asc limit 0,1) nextshowtime tv_channels c c.status="enable" order c.chn_name limit 0,10

here, there 2 tables named "tv_channels" , "tv_showtime". need 1 record each channel @ time ( current time). here suppose 12 channels , approx 30 (may vary foe each channel) records each channel , need display channels current show (more clarification: channels displayed has current show time and/or next show time.)

problem: need more field values "tv_showtime" display other required values. , if utilize way have write more inner select query , slow downwards website load. can suggest or advise other way write query please?

database table detail:

tv_channels [id, chn_name, [other required fields]], tv_showtime [id, chn_id, showtime, show_name, hits, last_ip [and few more fields]]

please allow me know if need farther detail question. help or suggestion appreciated. thanks.

as asked, didnt respond "end time" each show, had go on premise show time when started. said, how determine current show running given channel based on curtime() (instead of fixed time value).

get each channel , maximum show time exists prior current time...

likewise, how next show? each channel minimum show time starts after current time.

so, if had next records 1 channels , current time 2:15pm

channel showtime show_name 1 12:30pm show "x" 1 01:00pm show "b" 1 01:30pm show "c" 1 02:00pm show "d" <- current show 1 02:30pm show "y" <- next show 1 03:00pm show "z"

the current show running latest 1 prior 2:15 (show "d" starting @ 2pm) , next show first after current time (show "y" starting @ 2:30pm). above work if rows not in sequential order using min() , max() respectively time.

so, start channel table , left-join each separate pre-aggregate query detecting current show , next show times respectively , bring together on channel id each homecoming @ 1 record --- provided there record within qualified curtime() consideration.

from that, re-joining result sets actual tv schedule table again, time, on channel , time matched corresponding current or next time.

so now, have lined ready go respective aliases content. now, grab columns want present.

since joins left-joins, each side have null values, might want adjust query prevent nulls using coalesce(), such i've sampled...

select tc.id, tc.chn_name, tc.chn_logo, coalesce( curshowtimedetail.showtime, 'no time' ) curshowtime, coalesce( curshowtimedetail.show_name, '' ) curshowname, coalesce( curshowtimedetail.hits, 0 ) curhits, coalesce( nextshowtimedetail.showtime, 'no time' ) nextshowtime, coalesce( nextshowtimedetail.show_name, '' ) nextshowname, coalesce( nextshowtimedetail.hits, 0 ) nexthits tv_channels tc left bring together ( select st.chn_id, max( st.showtime ) curshowtime tv_showtime st st.showtime < curtime() grouping st.chn_id ) currentshow on tc.id = currentshow.chn_id left bring together tv_showtime curshowtimedetail on currentshow.chn_id = curshowtimedetail.chn_id , currentshow.curshowtime = curshowtimedetail.showtime left bring together ( select st.chn_id, min( st.showtime ) nextshowtime tv_showtime st st.showtime > curtime() grouping st.chn_id ) nextshow on tc.id = nextshow.chn_id left bring together tv_showtime nextshowtimedetail on nextshow.chn_id = nextshowtimedetail.chn_id , nextshow.nextshowtime = nextshowtimedetail.showtime

mysql join multi-select

Comments

Post a Comment

Popular posts from this blog

web services - java.lang.NoClassDefFoundError: Could not initialize class net.sf.cglib.proxy.Enhancer -

web services - java.lang.NoClassDefFoundError: Could not initialize class net.sf.cglib.proxy.Enhancer - when trying request rest service @ time of response getting exception.. feb 21, 2013 2:34:49 pm com.sun.jersey.spi.container.containerresponse mapmappablecontainerexception severe: exception contained within mappablecontainerexception not mapped response, re-throwing http container java.lang.noclassdeffounderror: not initialize class net.sf.cglib.proxy.enhancer @ org.hibernate.proxy.pojo.cglib.cgliblazyinitializer.getproxyfactory(cgliblazyinitializer.java:117) @ org.hibernate.proxy.pojo.cglib.cglibproxyfactory.postinstantiate(cglibproxyfactory.java:43) @ org.hibernate.tuple.entity.pojoentitytuplizer.buildproxyfactory(pojoentitytuplizer.java:162) @ org.hibernate.tuple.entity.abstractentitytuplizer.<init>(abstractentitytuplizer.java:135) @ org.hibernate.tuple.entity.pojoentitytuplizer.<init>(pojoentitytuplizer.java:55) @ org.hibernate.tuple.e...
Read more

Accessing MATLAB's unicode strings from C -

Accessing MATLAB's unicode strings from C - how can access underlying unicode info of matlab strings through matlab engine or mex c interfaces? here's example. let's set unicode characters in utf-8 encoded file test.txt, read as fid=fopen('test.txt','r','l','utf-8'); s=fscanf(fid, '%s') in matlab. now if first feature('defaultcharacterset', 'utf-8') , c engevalstring(ep, "s") , output text file utf-8. proves matlab stores unicode internally. if mxarraytostring(enggetvariable(ep, "s")) , unicode2native(s, 'latin-1') give me in matlab: non-latin-1 characters replaced character code 26. need getting access underlying unicode info c string in unicode format (utf-8, utf-16, etc.), , preserving non-latin-1 characters. is possible? my platform os x, matlab r2012b. addendum: documentation explicitly states "[mxarraytostring()] supports multibyte encoded characters...
Read more

javascript - mongodb won't find my schema method in nested container -

javascript - mongodb won't find my schema method in nested container - i trying access method of schema stored within mixed container. here situation : have cases model can many different things, have schema each of these things stored in "casecontent" mixed property. var caseschema = mongoose.schema({ casecontent : {}, object : {type:string, default : "null"}, collision : {type : boolean, default : false} }); the casecontent property filled model of 1 of schemas, 1 exemple : var treeschema = new mongoose.schema({ applecount : {type : number, default : 3} }); treeschema.methods.dostuff = function (data) { console.log('hey, listen'); homecoming true; }; then, want utilize method of schema original container : caseschema.methods.dostuff = function (data) { if (this.casecontent.dostuff !== undefined) { this.casecontent.dostuff(); console.log('it worked'); } else { ...
Read more