sql - How to select id's wich contains just special values? -

-

i need sql query. have table this:

id          booktype   date ----------- ---------- ------ 1           85       01.01.2014 1           86       01.01.2014 1           88       01.01.2014 1           3005     01.01.2014 1           3028     01.01.2014 2           74       01.01.2016 2           85       01.01.2016 2           86       01.01.2016         3           88       01.01.2015 3           3005     01.01.2015

i need query, returns id's booktype 85, 86 , not id's booktype 88,3005,3028. other types not relevant, can included.

example:

i want id 2, because there no booktype of 88, 3005, 3028. have id 74, doesn't matter, can included.

i tried this:

select bookid id, count(bookid) number books date between '01.01.2014' , '01.01.2016'   , booktype in (85,86) group bookid having count(bookid) >1 minus select bookid id, count(bookid) number books date between '01.01.2014' , '01.01.2016'   , booktype in (88,3005,3028) group bookid;

it doesn't work. every time results booktype 88 or other included. tried except, oracle sql developer doesn't know ist.

any ideas?

i see inconsistency between sql , columns names.

there no bookid in table , miss booktype...

so assuming first query is:

select id id, count(id) number books date between '2014-01-01' , '2016-01-01' , booktype in (85,86) group id having count(id) >1;

this have result set:

id        number  1          2  2          2

your second query

select id id, count(id) number books date between '2014-01-01' , '2016-01-01' , booktype in (88, 3005, 3028) group id;

this have result set:

id        number  1          3  3          2

the minus operator in oracle returns unique rows returned first query not second. whole query return first record set both results of first query different results of second.

if drop count statement in query have:

first query

select id id books date between '2014-01-01' , '2016-01-01' , booktype in (85,86) group id having count(id) >1;

result set

id   1            2

second query:

select id id books date between '2014-01-01' , '2016-01-01' , booktype in (88, 3005, 3028) group id;

result set

id   1            3

and applying minus operator 2 required, 1 in second result set.

this confirm logic right, did not take in account way minus operates on result sets.

so query has be:

select id id books date between '2014-01-01' , '2016-01-01' , booktype in (85,86) group id having count(id) >1 minus select id id books date between '2014-01-01' , '2016-01-01' , booktype in (88, 3005, 3028) group id;

last remarks:

  • i left date between '2014-01-01' , '2016-01-01', thought relative other requirements if not relevant in example
  • i left having count(id) >1, thought relative other requirements if not relevant in example

regards


Comments

Post a Comment

Popular posts from this blog

java - Run spring boot application error: Cannot instantiate interface org.springframework.context.ApplicationListener -

-
i have spring project , try make use spring boot , run @ embadded tomcat following : https://spring.io/guides/gs/rest-service/ this application //@configuration //@enableaspectjautoproxy @springbootapplication @componentscan(basepackages = "gux.prome") public class application { public static void main(string[] args) { springapplication.run(application.class, args); } } if use maven command: mvn spring-boot:run , project starts fine, need debug , run main method @ intelij, exception occurs: exception in thread "main" java.lang.illegalargumentexception: cannot instantiate interface org.springframework.context.applicationlistener : org.springframework.boot.logging.classpathloggingapplicationlistener @ org.springframework.boot.springapplication.createspringfactoriesinstances(springapplication.java:414) @ org.springframework.boot.springapplication.getspringfactoriesinstances(springapplication.java:394) @ org.springframework.bo...
Read more

reactjs - React router and this.props.children - how to pass state to this.props.children -

-
i'm using react-router first time , don't know how think in yet. here's how i'm loading components in nested routes. entry point .js reactdom.render( <router history={hashhistory} > <route path="/" component={app}> <route path="models" component={content}> </route> </router>, document.getelementbyid('app') ); app.js render: function() { return ( <div> <header /> {this.props.children} </div> ); } so child of app content component sent in. i'm using flux , app.js has state , listens changes, don't know how pass state down this.props.children. before using react-router app.js defines children explicitly, passing state natural don't see how now. using couple of react helper methods can add state, props , whatever else this.props.children render: function() { var children = react.ch...
Read more

Excel VBA "Microsoft Windows Common Controls 6.0 (SP6)" Location Changes -

-
i have excel workbook used generate quotations customers. there main page user inputs data, , there buttons generate various pdfs , send them customer. however, 1 user consistently getting error preventing generating , sending pdfs. i beleive "microsoft windows common controls 6.0 (sp6)". it's installed on users' computers. on mine, location "c:\windows\syswow64\mscomctl.ocx" on others "c:\windows\system32\mscomctl.ocx" the user experiencing issues on 32-bit machine, "mscomctl.ocx" file should in system32. have manually put in there, , pointed common controls reference in vba it. issue seems keep reverting looking in syswow64. since folder doesn't exist, can't find it, , can't produce pdfs. i thought perhaps issue user running older version of office (2007) there users on 2013 , 2016. thought perhaps mixture of 64-bit , 32-bit users. user gets sent workbook other users (as quotation process goes further, gets passed ...
Read more