I feel like I've been staring at this statement for hours and just keep mangling it more. Any help would be appreciated.I want to select all records from tipTransactionPivot and salesExport. If there are the same values for rvcID, employeeID, and businessDate, then I want to join them. Otherwise, I'd like to return them with null values for the other table. I also want to get certain values from the employee table joined on either the microsEmployeeNumber (in the case of salesExport) or employeeID (in the case of tipTransactionPivot).Like I said, I probably have this pretty mangled at this point, so I'm sure there is a better way to do it. P.S. I know it would be better to do some of this in an application rather than in SQL, but it isn't really an option, so I need to return SQL results from a single query if at all possible.SELECT dbo.salesExport.businessDate, dbo.salesExport.rvcID, dbo.employee.employeeID, dbo.employee.employeeFName, dbo.employee.employeeLName, dbo.salesExport.chargedReceipts, dbo.salesExport.grossReceipts - dbo.salesExport.chargedReceipts AS otherReceipts, dbo.salesExport.grossReceipts, dbo.salesExport.discountTotal, dbo.salesExport.bqtSvc19, dbo.salesExport.bqtSvc20, CONVERT(numeric(18, 2), dbo.salesExport.bqtSvc19 / 0.19 * - 1) AS bqt19Sales, CONVERT(numeric(18, 2), dbo.salesExport.bqtSvc20 / 0.20 * - 1) AS bqt20Sales, CONVERT(numeric(18, 2), ((dbo.salesExport.grossReceipts + dbo.salesExport.discountTotal) + dbo.salesExport.bqtSvc19 / 0.19 * - 1) + dbo.salesExport.bqtSvc20 / 0.20 * - 1) AS netSales, dbo.salesExport.chargedTips, dbo.employee.microsEmployeeNumber, ISNULL(dbo.tipTransactionPivot.cashTips, 0) AS cashTips, ISNULL(dbo.tipTransactionPivot.cashTipsOut, 0) AS cashTipsOut, ISNULL(dbo.tipTransactionPivot.cashTipsIn, 0) AS cashTipsIn, ISNULL(dbo.tipTransactionPivot.chargeTipsOut, 0) AS chargeTipsOut, ISNULL(dbo.tipTransactionPivot.chargeTipsIn, 0) AS chargeTipsInFROM dbo.employee AS employee_1 INNER JOIN dbo.tipTransactionPivot ON employee_1.employeeID = dbo.tipTransactionPivot.employeeID FULL OUTER JOIN dbo.employee RIGHT OUTER JOIN dbo.salesExport ON dbo.employee.microsEmployeeNumber = dbo.salesExport.microsEmployeeNumber ON employee_1.microsEmployeeNumber = dbo.salesExport.microsEmployeeNumber AND dbo.tipTransactionPivot.businessDate = dbo.salesExport.businessDate AND dbo.tipTransactionPivot.rvcID = dbo.salesExport.rvcID
I Think This Join Statement is Making Me Crazy(er)
Posted - 2013-02-20 : 21:31:48