mysql - fetch records from a table to which it is having similar ids -

-

i have 2 tables like

   uid name     11  cadman     12  clive     13  coleman     14  chester

and second table like

id      pa1 pa2 pa3 pa4 4800    11  12  11  14 4801    11  12  13  14 4802    11  12  12   4973        12  13  14 6882    12  12  13  14 6883    11  12      14 6884    11  13  13  14 6885    11  13  13  14

i display names instead of ids can 1 me in query in advance

create / populate

create table nt (   -- name table     uid int auto_increment primary key,     name varchar(100) not null );  create table (   -- id table (sorry creativity @ low)     id int primary key,     pa1 int not null,     pa2 int not null,     pa3 int not null,     pa4 int not null,     -- in constraining kinda mood:     constraint fk_pa1_nt foreign key (pa1) references nt(uid),     constraint fk_pa2_nt foreign key (pa2) references nt(uid),     constraint fk_pa3_nt foreign key (pa3) references nt(uid),     constraint fk_pa4_nt foreign key (pa4) references nt(uid) );  insert nt (name) values ('kurt'),('burt'),('curt'),('dirt'),('lert'),('murte'),('qbert'); insert nt (name) values ('jim'),('kim'),('dim'),('sim'),('tim'),('cadman'),('bim');  insert it(id,pa1,pa2,pa3,pa4) values (4800,11,12,13,14); insert it(id,pa1,pa2,pa3,pa4) values (4801,3,5,7,9);

query

select it.id,nt1.name,nt2.name,nt3.name,nt4.name join nt nt1 on nt1.uid=it.pa1 join nt nt2 on nt2.uid=it.pa2 join nt nt3 on nt3.uid=it.pa3 join nt nt4 on nt4.uid=it.pa4 order it.id; +------+------+------+--------+------+ | id   | name | name | name   | name | +------+------+------+--------+------+ | 4800 | sim  | tim  | cadman | bim  | | 4801 | curt | lert | qbert  | kim  | +------+------+------+--------+------+ 2 rows in set (0.00 sec)

Comments

Post a Comment

Popular posts from this blog

Fail to load namespace Spring Security http://www.springframework.org/security/tags -

-
i have problem namespace of spring security on xhtml page. don't understand why can't load uri, , have following issue: whitelabel error page application has no explicit mapping /error, seeing fallback. tue jul 21 16:58:12 utc 2015 there unexpected error (type=not found, status=404). please need help! possible uri has been changed? best regards you using incorrect namespace url. correct url http://www.springframework.org/schema/security the error page seeing spring source website since not recognize url. try visiting http://www.springsource.org/security/tags in browser update : spring security tags used in jsp page correct tag lib declaration is: <%@ taglib prefix="sec" uri="http://www.springframework.org/security/tags" %>
Read more

sql - MySQL query optimization using coalesce -

-
can explain me why second query faster first? (first 1 takes around 40 seconds, second 1 less 0.1) select releases.feature_code, releases.platform_id, version, release_date, general_available, uri, filesize, display_name, feature_name.full_name, if ((select exists(select 1 release_exceptions releases.feature_code = release_exceptions.feature_code , releases.platform_id = release_exceptions.platform_id , releases.version = release_exceptions.version) = 1), 1, 0) release_exceptions releases left outer join platform using(platform_id) left outer join feature_name using(feature_code) order version desc; ~~~ query 2 ------------------ ~~~ select feature_code, version, release_date, general_available, platform_id, uri, filesize, display_name, feature_name.full_name, coalesce(release_exceptions, 0) release_exceptions mus.releases left outer join ( select distinct feature_code, version, platform_id, 1 release_exceptions mus.release_exceptions ) release_exceptions using ...
Read more

unity3d - Unity local avoidance in user created world -

-
i'm using unity3d networked multiplayer online game have large complex 3d terrain scene forest, trees, cliff, hills, mountains, bounders, etc. players can build structures sort of minecraft, , put them anywhere in scene, or move them around anytime. aside human controlled players, there automated ai players , objects animals roaming around scene following path. the problem how make these automated ai players , animals, able navigate around static , dynamic player created structures, because path follow can blocked player created structures, or other players , other ai objects, cliffs etc. have find away around them or on track if tumble down off high cliff example. so made navmesh , used navagents, takes care of static, non moving objects, how make ai players navigate around each other , dynamic structures created players can number in hundreds? i thought of adding navmeshobstacle everything, result in being attached hundreds of objects since user created structures b...
Read more