HELP PLEASE! SQL not giving me all the data i need!

Language Help SQL
1

Hi Everyone,

I hope you can help, im hoping there is a simple solution to this and i dont need to start messing with UNION etc.

I created a report with SQL to generate a quotation which worked fine. however i have recent made some changes so the report can pick up whether it should actually be a quote or a Proforma invoice. This works fine.

The next change i made is either tho we are quoting or issuing the proforma to customer A, the work may be carried out for customer B so i again updated the report to choose the delivery address depending on whether it is a Standard quote or a Field Service quote.

Now this works fine IF it is a field service quote but if it is not i get a blank report…UNLESS, i make the quote Field Service then switch it back to Standard then it will work. I know the issue is because 3 columns in one of the tables are null if the quote has never been a Field Service type and i have tried a variety of ways within the joins to bring all the data regardless but im stuggling…i have put my code below, can anyone see something i am missing? the joins i bold are what seem to be causing me the problem. Thanks in advance.

SELECT
							orders.id,
							orders.traderid,
							orders.orderdate,
							orders.tradercontactid,
							orders.currencyid,
							orders.invoiceaddressid,
							orders.acknowledgementaddressid,
							orders.deliveryaddressid,
							orders.orderaddressid,
							orders.validtilldate,
							orders.revision,
							orders.source,
							quotation_custom.quotationtype,
							orderitems.sequencenumber,
							orderitems.itemnumber,
							orderitems.groupitemnumber,
							orderitems.isprimeitem,
							orderitems.partid,
							orderitems.unitprice,
							orderitems.foreignunitprice,
							orderitems.foreignnettprice,
							orderitems.traderunitprice,
							orderitems.foreigntraderunitprice,
							orderitems.foreignvatvalue,
							orderitems.discountpercentage,
							orderitems.duedate,
							orderitems.traderquantity,
							orderitems.stockedquantity,
							orderitems.traderorderreference,
							orderitems.traderpartreference,
							orderitems.traderuom,
							orderitems.nettprice,
							orderitems.vatvalue,
							(orderitems.nettprice + orderitems.vatvalue) as grossvalue,
							(orderitems.foreignnettprice + orderitems.foreignvatvalue) as fgrossvalue,
							traders.discterms,
							currency.curdesc,
							currency.currvaldp,
							currency.symbol,
							partmaster.uom,
							orderitems.description,
							users.name,
							traderunitsofmeasure.uomfact,
							userinfo.userid,
							spmatrix.spmcoforigin,
							sitetrader.id AS siteid,
							sitetrader.name AS sitename,
							site.street AS sitestreet,
							site.city AS sitecity,
							site.county AS sitecounty,
							site.postcode AS sitepostcode,
							site.vatreg AS sitevatreg,
							ecsite.eccdesc AS sitecountry,
							telephone.fixednumber AS sitetelephone,
							fax.fixednumber AS sitefax,
							email.destination AS siteemail,
							traders.name AS tradername,
							tradersaddresses.street AS traderstreet, /*ackadd*/
							tradersaddresses.city AS tradercity, /*ackadd*/
							tradersaddresses.county AS tradercounty, /*ackadd*/
							tradersaddresses.postcode AS traderpostcode, /*ackadd*/
							tradersaddresses.vatreg AS tradervatreg, /*ackadd*/
							tradersaddresses.isexportaddress AS isexportaddress,
							tradersaddresses.ismainaddress AS tismain,
							traderscountry.eccdesc AS tradercountry,
							traderscountry.eccineu AS traderineu,
							shipmentaddresses.street AS delstreet, /*deladd*/
							shipmentaddresses.city AS delcity, /*deladd*/
							shipmentaddresses.county AS delcounty, /*deladd*/
							shipmentaddresses.postcode AS delpostcode, /*deladd*/
							shipmentaddresses.ismainaddress AS ismain,
							shipmentcountry.eccdesc AS delcountry, /*deladd*/
       					        	shipmentcontact.title AS deltitle, /*deladd*/
	   						shipmentcontact.firstname AS delfirstname, /*deladd*/
	   						shipmentcontact.lastname AS dellastname, /*deladd*/
	   						traderscontacts.title AS title, /*ackadd*/
	   						traderscontacts.firstname AS firstname, /*ackadd*/
	   						traderscontacts.lastname AS lastname, /*ackadd*/
                                                        jobaddress.street AS jobstreet,
	   						jobaddress.city AS jobcity,
	   						jobaddress.county AS jobcounty,
	   						jobaddress.postcode AS jobpostcode,
	   						jobaddress.ismainaddress AS jismain,
							jobcountry.eccdesc AS jcountry,
							jtrader.name AS jname,
	   						jobcontact.title AS jobtitle,
	   						jobcontact.firstname AS jobfirstname,
	   						jobcontact.lastname AS joblastname,
						FROM quotations orders
							INNER JOIN quotationitems orderitems ON
								orders.id = orderitems.orderid AND
								orderitems.ordertype = orders.ordertype
							INNER JOIN traders ON
								orders.traderid = traders.id AND
								orders.tradertype = traders.tradertype
							LEFT OUTER JOIN currency ON
								orders.currencyid = currency.currid
							LEFT OUTER JOIN users ON
								users.id = orders.responsibility
							LEFT OUTER JOIN allpartmaster partmaster ON
								orderitems.partid = partmaster.partnum
							LEFT OUTER JOIN traderunitsofmeasure ON
								traderunitsofmeasure.traderid = orders.traderid AND
								traderunitsofmeasure.uompart = orderitems.partid AND
								traderunitsofmeasure.uomid = partmaster.uom AND
								traderunitsofmeasure.uomtarget = orderitems.traderuom
							LEFT OUTER JOIN userinfo ON
								orders.responsibility = userinfo.userid
							LEFT OUTER JOIN spmatrix ON
								orders.traderid = spmatrix.spmsupp AND
								orderitems.partid = spmatrix.spmpart
							INNER JOIN tradersaddresses site ON
								site.tradertype = 'HQ' AND site.ismainaddress = 1
							INNER JOIN traders sitetrader ON
								site.traderid = sitetrader.id AND site.tradertype = sitetrader.tradertype
							INNER JOIN eccountry ecsite ON
								site.countryid = ecsite.ecccode
							LEFT OUTER JOIN traderscontacts sitecontact ON
								site.traderid = sitecontact.traderid AND
								site.addressid = sitecontact.mainaddressid AND
								sitecontact.contactid = 'SALES'
							LEFT OUTER JOIN contactcommunications telephone ON
								sitecontact.contactid = telephone.contactid AND
								site.traderid = telephone.traderid AND
								site.tradertype = telephone.tradertype AND
								telephone.communicationmethodid = 'Business telephone'
							LEFT OUTER JOIN contactcommunications fax ON
								sitecontact.contactid = fax.contactid AND
								site.traderid = fax.traderid AND
								site.tradertype = fax.tradertype AND
								fax.communicationmethodid = 'Business fax'
							LEFT OUTER JOIN contactcommunications email ON
								sitecontact.contactid = email.contactid AND
								site.traderid = email.traderid AND
								site.tradertype = email.tradertype AND
								email.communicationmethodid = 'Email address'
							INNER JOIN quotation_custom ON
								quotation_custom.id = orders.id								
							LEFT OUTER JOIN tradersaddresses ON /*ACK ADDRESS JOIN*/
								orders.traderid = tradersaddresses.traderid AND
								traders.tradertype = tradersaddresses.tradertype AND
								orders.acknowledgementaddressid = tradersaddresses.addressid 
							LEFT OUTER JOIN eccountry traderscountry ON
								tradersaddresses.countryid = traderscountry.ecccode
							LEFT OUTER JOIN tradersaddresses shipmentaddresses ON /*DEL ADDRESS JOIN*/
								orders.traderid = shipmentaddresses.traderid AND
								traders.tradertype = shipmentaddresses.tradertype AND
								orders.deliveryaddressid = shipmentaddresses.addressid 
							LEFT OUTER JOIN eccountry shipmentcountry ON
								shipmentaddresses.countryid = shipmentcountry.ecccode
							LEFT OUTER JOIN traderscontacts shipmentcontact ON /* DEL CONTACT JOIN*/
								shipmentaddresses.traderid = shipmentcontact.traderid
								AND shipmentaddresses.tradertype = shipmentcontact.tradertype
								AND shipmentaddresses.addressid = shipmentcontact.mainaddressid
								AND shipmentcontact.ismainaddresscontact = 1
							LEFT OUTER JOIN traderscontacts ON /* ACK CONTACT JOIN */
								orders.tradercontactid = traderscontacts.contactid
								AND tradersaddresses.tradertype = traderscontacts.tradertype
								AND orders.acknowledgementaddressid = traderscontacts.mainaddressid
								AND orders.traderid = traderscontacts.traderid							
							**LEFT OUTER JOIN tradersaddresses jobaddress ON /* JOB ADDRESS JOIN*/**
								**quotation_custom.fieldservicecustomerid = jobaddress.traderid**
								**AND orders.tradertype = jobaddress.tradertype**
								**AND quotation_custom.fieldserviceaddressid = jobaddress.addressid**
							LEFT OUTER JOIN eccountry jobcountry ON
								**jobaddress.countryid = jobcountry.ecccode**
					****LEFT OUTER JOIN traderscontacts jobcontact ON /*JOB CONTACT JOIN*/**
							 	**quotation_custom.fieldservicecustomerid = jobcontact.traderid**
							    **AND orders.tradertype = jobcontact.tradertype**
								**AND quotation_custom.fieldserviceaddressid = jobcontact.mainaddressid**
								**AND jobcontact.ismainaddresscontact = 1**
							**INNER JOIN traders jtrader ON**
								**quotation_custom.fieldservicecustomerid = jtrader.id**
								**AND orders.tradertype = jtrader.tradertype**
						WHERE
							orders.id = 'Q-00000'
							AND
							orderitems.outcome = 'PENDING'
						ORDER BY 
							orderitems.itemnumber
2

There’s a lot going on here. Frankly, 104 return columns is a little much.

I’d take out all the whereish stuff from the joins first to see if you get all results from the joins without filters.

Hmm, I’d also combine some of those multiple table hits.

Consider this snip of what you have:

select
    telephone.fixednumber AS sitetelephone,
    fax.fixednumber AS sitefax,
    email.destination AS siteemail
    ...
    JOIN tradersaddresses site 
        ON site.tradertype = 'HQ' AND site.ismainaddress = 1
    LEFT JOIN traderscontacts sitecontact
        ON site.traderid = sitecontact.traderid
            AND site.addressid = sitecontact.mainaddressid
            AND sitecontact.contactid = 'SALES'
    LEFT JOIN contactcommunications telephone
        ON sitecontact.contactid = telephone.contactid 
            AND site.traderid = telephone.traderid
            AND site.tradertype = telephone.tradertype
            AND telephone.communicationmethodid = 'Business telephone'
    LEFT JOIN contactcommunications fax
        ON sitecontact.contactid = fax.contactid
            AND site.traderid = fax.traderid
            AND site.tradertype = fax.tradertype
            AND fax.communicationmethodid = 'Business fax'
    LEFT JOIN contactcommunications email
        ON sitecontact.contactid = email.contactid
            AND site.traderid = email.traderid
            AND site.tradertype = email.tradertype
            AND email.communicationmethodid = 'Email address'

By “whereish stuff” I mean the literals like site.tradertype = 'HQ' AND site.ismainaddress = 1. Indeed, a join..on without an actual join should be viewed with deep suspicion.

I’d make the above a sub query something like:

select
    site.traderid, site.tradertype, sitecontact.contactid,
        telephone.fixednumber AS sitetelephone,
        fax.fixednumber AS sitefax,
        email.destination AS siteemail
    from tradersaddresses site 
        LEFT JOIN traderscontacts sitecontact ON site.traderid = sitecontact.traderid AND site.addressid = sitecontact.mainaddressid
        LEFT JOIN contactcommunications telephone ON site.traderid = telephone.traderid AND site.tradertype = telephone.tradertype AND sitecontact.contactid = telephone.contactid
                AND telephone.communicationmethodid = 'Business telephone'
        LEFT JOIN contactcommunications fax ON site.traderid = fax.traderid AND site.tradertype = fax.tradertype AND sitecontact.contactid = fax.contactid
                AND fax.communicationmethodid = 'Business fax'
        LEFT JOIN contactcommunications email ON site.traderid = email.traderid AND site.tradertype = email.tradertype
            AND sitecontact.contactid = email.contactid AND email.communicationmethodid = 'Email address'
    where site.tradertype = 'HQ' AND site.ismainaddress = 1 AND sitecontact.contactid = 'SALES'

Or, you can roll those numbers up… even the email? I could be wrong, but I believe this will work:

select
        site.traderid, site.tradertype, sitecontact.contactid,
        max(case cc.communicationmethodid when 'Business telephone' then cc.fixednumber else null end) AS sitetelephone,
        max(case cc.communicationmethodid when 'Business fax' then cc.fixednumber else null end) AS sitefax,
        max(case cc.communicationmethodid when 'Email address' then cc.destination else null end) AS siteemail
    from tradersaddresses site 
        LEFT JOIN traderscontacts sitecontact ON site.traderid = sitecontact.traderid AND site.addressid = sitecontact.mainaddressid
        LEFT JOIN contactcommunications cc ON site.traderid = cc.traderid AND site.tradertype = cc.tradertype AND sitecontact.contactid = cc.contactid
    where site.tradertype = 'HQ' AND site.ismainaddress = 1 AND sitecontact.contactid = 'SALES'
    group by site.traderid, site.tradertype, sitecontact.contactid

Break your mammoth query that doesn’t work into smaller chunks of logic that do. Then, roll those back together.

1 Like