sql - Left Outer Join Query returns too many rows -

-

i'm having issue trying data 4 tables. i've tried 4 left outer joins , i'm trying subselect. try ad.accountdirecttocommitmentitemcategoryid field, query goes returning desired 4 rows 16 rows. i'm trying add column results duplicating each row each data in column. how should writing query differently?

  select distinct  ad.accountdirecttocommitmentitemcategoryid, cic.commitmentitemcategoryname, cict.commitmentitemcategorytypename, cic.commitmentitemcategorytypeid, cic.commitmentitemcategoryid, vw.dollarsallocated   [dbo].[tblcommitmentitemcategory] cic   left outer join [dbo].vw_parentaccountdollarsallocatedbycommitmentitemcategory] vw    on vw.commitmentitemcategoryid = cic.commitmentitemcategoryid    left outer join [dbo].[tblcommitmentitemcategorytype] cict    on cic.commitmentitemcategorytypeid = cict.commitmentitemcategorytypeid      left outer join (     select distinct accountdirecttocommitmentitemcategoryid, accountdirectparentid [dbo].[tblaccountdirecttocommitmentitemcategory] adtcic     adtcic.accountdirectparentid = 6478) ad    on     /*(ad.accountdirecttocommitmentitemcategoryid = vw.commitmentitemcategoryid)   gets 4 rows first column null */    (ad.accountdirectparentid = vw.accountdirectparentid)  /*returns 16 rows */    fiscalyear = 2015 , vw.accountdirectparentid = 6478    order cic.commitmentitemcategoryname

it wouldn't let me post image of results because i'm newbie. hope helps...

accountdirecttocommitmentitemcategoryid commitmentitemcategoryname  commitmentitemcategorytypename  commitmentitemcategorytypeid    commitmentitemcategoryid    dollarsallocated 6   23**    contractual 1   2   1200.00 7   23**    contractual 1   2   1200.00 8   23**    contractual 1   2   1200.00 9   23**    contractual 1   2   1200.00 6   25**    contractual 1   1   1100.00 7   25**    contractual 1   1   1100.00 8   25**    contractual 1   1   1100.00 9   25**    contractual 1   1   1100.00 6   26**    supplies    3   3   1300.00 7   26**    supplies    3   3   1300.00 8   26**    supplies    3   3   1300.00 9   26**    supplies    3   3   1300.00 6   31**    equipment   2   5   500.00 7   31**    equipment   2   5   500.00 8   31**    equipment   2   5   500.00 9   31**    equipment   2   5   500.00

linq solution: i'm not getting 0 dollars row when no category match: able figure out offending column parentid needing specfied again in clause. , created linq query it. unfortunately i'm not getting 0 dollars entry when there no matching cic category used before. thoughts? 

var querydollars = cic in johncontext.tblcommitmentitemcategory    join vw in johncontext.vw_parentaccountdollarsallocatedbycommitmentitemcategory         on new { commitmentitemcategory = cic.commitmentitemcategoryid, fiscalyear = 2015, parentaccountid = 6478 }             equals new { commitmentitemcategory = vw.commitmentitemcategoryid, fiscalyear = vw.fiscalyear, parentaccountid = vw.accountdirectparentid }    t    vw in t.defaultifempty()    join cict in johncontext.tblcommitmentitemcategorytype    on cic.commitmentitemcategorytypeid equals cict.commitmentitemcategorytypeid    join ad in johncontext.tblaccountdirecttocommitmentitemcategory    on vw.commitmentitemcategoryid equals ad.commitmentitemcategoryid    ad.accountdirectparentid == 6478    let displaycategory = cic.commitmentitemcategoryname + " ( " + cict.commitmentitemcategorytypename + " )" // called categorytype it's id below    let displaycategorytypeid = "categorytype_" + ad.accountdirecttocommitmentitemcategoryid.tostring() //   let displaycategorytypeid = "categorytype_" + cic.commitmentitemcategoryid.tostring()    let displaydollarsallocated = vw.dollarsallocated == null ? 0 : vw.dollarsallocated    orderby cic.commitmentitemcategoryname    select new    {        displaycategory,        displaycategorytypeid,        cic.commitmentitemcategoryid,        displaydollarsallocated    };


Comments

Post a Comment

Popular posts from this blog

c++ - No viable overloaded operator for references a map -

-
i trying use map can make tag names reference number. when try , use it, in code error (6 in total every time reference map): src/main.cpp:25:45: error: no viable overloaded operator[] type 'std::map<std::string, std::string>' const char* idcs = node.child_value(tagmap[3]); this code: #include "pugi/pugixml.hpp" #include <iostream> #include <string> #include <map> int main() { pugi::xml_document doca, docb; std::map<std::string, pugi::xml_node> mapa, mapb; std::map<std::string, std::string> tagmap {{"1", "data"}, {"2", "entry"}, {"3", "id"}, {"4", "content"}}; if (!doca.load_file("a.xml") || !docb.load_file("b.xml")) { std::cout << "can't find input files"; return 1; } (auto& node: doca.child(tagmap[1]).children(tagmap[2])) { co
Read more

java - Custom OutputStreamAppender not run: LOGBACK: No context given for <MYAPPENDER> -

-
i wish write custom appender on basis of outputstreamappender . wrote following class package tests; import java.io.ioexception; import java.io.outputstream; import ch.qos.logback.core.outputstreamappender; public class myappender<e> extends outputstreamappender<e> { public myappender() { system.out.println("myappender created"); setoutputstream(new outputstream() { @override public void write(int b) throws ioexception { throw new unsupportedoperationexception(); } }); } } then wrote following runner: package tests; import org.slf4j.loggerfactory; import ch.qos.logback.classic.logger; public class runner { private static final logger log = (logger) loggerfactory.getlogger(runner.class); public static void main(string[] args) { //log.addappender(new myappender<iloggingevent>()); log.info("hello world"); } } finall
Read more

java - Cannot secure connection using TLS -

-
i'm trying establish secure socket connection between java client applet (built jdk 1.7.0_75-b13) , vc++ server application. as test vehicle, used vc++ client/server sample found in msdn forums, modified use schannel , able establish socket using cipher suite tls_rsa_with_aes_128_cbc_sha. works of tls 1.0/1.1/1.2. when try opening socket java applet same server application, connection rejected server reporting following: tls 1.0 acceptsecuritycontext failed: 0x80090327 tls 1.1 acceptsecuritycontext failed: 0x80090331 tls 1.2 acceptsecuritycontext failed: 0x80090331 this java code used create socket: debugprint("setting secure connection"); sslsocketfactory sslsocketfactory = (sslsocketfactory) sslsocketfactory.getdefault(); sslsocket sslsocket = (sslsocket) sslsocketfactory.createsocket("127.0.0.1", socketnumber); debugprint("starting handshake"); sslsocket.settcpnodelay(true); sslsocket.setsolinger(false, 0); sslsocket.s
Read more