sql server 2008 - Get only rows where 2 conditions are fulfilled in Microsoft SQL -

-

i have table mapping table t1. looks following:

+-------+---------+-------------+ | reqid | fieldid | listitemid  |  +-------+---------+-------------+ |  219  | 76      |    3548     | |  219  | 86      |    2382     | |  220  | 76      |    3548     | |  220  | 86      |    3491     | |  221  | 77      |    3550     | |  221  | 87      |    2387     | +-----------------+-------------+

now want todo select distinct reqids has both of following

select * t1  (fieldid='76' , listitemid='3548')     or (fieldid='77' , listitemid='3550')  or ((fieldid='86' , (listitemid='3491' or listitemid='2380'))  or (fieldid='87' , (listitemid='3494' or listitemid='2386'))) order requirementid

this gives me rows has 1 of above requirements. want todo get

 select * t1      ((fieldid='76' , listitemid='3548') or (fieldid='77' , listitemid='3550'))      ,  (((fieldid='86' , (listitemid='3491' or listitemid='2380'))      or (fieldid='87' , (listitemid='3494' or listitemid='2386')))) order requirementid

the above don't return rows. points want todo.

what query should return 220 since thats rows contain both fieldid=76 , listitemid=3548 , fieldid=86 , listitemid=3491.

is done in query or have write 2 separate querys , create 2 arrays , compare ids occurs in both arrays?

you had it. need select reqid matching conditions, , rows reqid. can accomplished sub-query.

done using 2 sub-queries:

select *  t1  reqid in  (     select t11.reqid      t1 t11              (t11.fieldid='76' , t11.listitemid='3548')          or (t11.fieldid='77' , t11.listitemid='3550') )  , reqid in  (     select t11.reqid      t1 t11               (t11.fieldid='86' , (t11.listitemid='3491' or t11.listitemid='2380'))          or (t11.fieldid='87' , (t11.listitemid='3494' or t11.listitemid='2386')) ) order requirementid

this can further translated single sub-query using join.

select *  t1  reqid in  (     select t11.reqid      t1 t11     join t1 t12 on t11.reqid = t12.reqid              ((t11.fieldid='76' , t11.listitemid='3548') or (t11.fieldid='77' , t11.listitemid='3550'))         ,         (             (t12.fieldid='86' , (t12.listitemid='3491' or t12.listitemid='2380'))              or (t12.fieldid='87' , (t12.listitemid='3494' or t12.listitemid='2386'))         ) )  order requirementid

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