Issue with joining tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Issue with joining tables

Author

Topic

DHL
Starting Member

16 Posts

Posted - 2008-05-14 : 08:57:00

Hello,

I am quite new to SQL, so forgive me if this is a stupid question, but I need to have the answer.

I make a join between 2 same tables (i.e. FactSummary), the reason is because this table contains invoice info and payment info, recognized by the field TransactionIDType (AP_INVOICES_ALL for invoices and AP_CHECKS_ALL for payments).
As I need to know whether invoices are already paid or not, I need to join FactSummary with Fact_Summary, which is not really a problem. But I am facing following issue:
Now I have a case where there is no payment done yet (so no info around this in the table), but there is invoice data... I however do not seem to get any records but I would like to have records without the payment date or where the payment date is empty. Below is my SQL statement...

Please help...

SELECT FactSummary.ERPInvoiceID, FactSummary.TransactionDate, FactSummary.TransactionIDType, FactSummary.DoxisNumber,
FactSummary.Currency, FactSummary.OrgID, FactSummary_1.TransactionIDType AS Expr2,
FactSummary_1.TransactionDate AS PaymentDate
FROM FactSummary LEFT OUTER JOIN
FactSummary AS FactSummary_1 ON FactSummary.ERPInvoiceID = FactSummary_1.ERPInvoiceID
WHERE (FactSummary.ERPInvoiceID = 40343) AND (FactSummary.TransactionIDType = 'AP_INVOICES_ALL') AND
(FactSummary_1.TransactionIDType = 'AP_CHECKS_ALL')

singularity
Posting Yak Master

153 Posts

Posted - 2008-05-14 : 09:02:28

SELECT FactSummary.ERPInvoiceID, FactSummary.TransactionDate, FactSummary.TransactionIDType, FactSummary.DoxisNumber,
FactSummary.Currency, FactSummary.OrgID, FactSummary_1.TransactionIDType AS Expr2,
FactSummary_1.TransactionDate AS PaymentDate
FROM FactSummary
LEFT OUTER JOIN
(SELECT ERPInvoiceID, TransactionIDType AS Expr2, TransactionDate AS PaymentDate FROM FactSummary
WHERE FactSummary_1.TransactionIDType = 'AP_CHECKS_ALL') AS FactSummary_1 ON FactSummary.ERPInvoiceID = FactSummary_1.ERPInvoiceID
WHERE (FactSummary.ERPInvoiceID = 40343) AND (FactSummary.TransactionIDType = 'AP_INVOICES_ALL')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-14 : 09:26:12

[code]SELECT fs.ERPInvoiceID,
fs.TransactionDate,
fs.TransactionIDType,
fs.DoxisNumber,
fs.Currency,
fs.OrgID,
d.TransactionIDType AS Expr2,
d.TransactionDate AS PaymentDate
FROM FactSummary AS fs
LEFT JOIN FactSummary AS d ON d.ERPInvoiceID = fs.ERPInvoiceID
AND d.TransactionIDType = 'AP_CHECKS_ALL'
WHERE fs.ERPInvoiceID = 40343
AND fs.TransactionIDType = 'AP_INVOICES_ALL'[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 09:38:55

[code]SELECT FactSummary.ERPInvoiceID,
MAX(CASE WHEN FactSummary.TransactionIDType ='AP_INVOICES_ALL' THEN FactSummary.TransactionDate ELSE NULL END) AS TransactionDate, MAX(CASE WHEN FactSummary.TransactionIDType ='AP_INVOICES_ALL' THEN FactSummary.TransactionIDType ELSE NULL END) as TransactionIDType, MAX(CASE WHEN FactSummary.TransactionIDType ='AP_INVOICES_ALL' THEN FactSummary.DoxisNumber ELSE NULL END) as DoxisNumber,
MAX(CASE WHEN FactSummary.TransactionIDType ='AP_INVOICES_ALL' THEN FactSummary.Currency ELSE NULL END) as Currency,
MAX(CASE WHEN FactSummary.TransactionIDType ='AP_INVOICES_ALL' THEN FactSummary.OrgID ELSE NULL END) as OrgID,
MAX(CASE WHEN FactSummary.TransactionIDType ='AP_CHECKS_ALL' THEN FactSummary.TransactionIDType ELSE NULL END)AS Expr2,
MAX(CASE WHEN FactSummary.TransactionIDType ='AP_CHECKS_ALL' THEN FactSummary.TransactionDate ELSE NULL END) AS PaymentDate
FROM FactSummary
WHERE (FactSummary.ERPInvoiceID = 40343) AND (FactSummary.TransactionIDType IN('AP_INVOICES_ALL','AP_CHECKS_ALL'))
GROUP BY FactSummary.ERPInvoiceID[/code]

DHL
Starting Member

16 Posts

Posted - 2008-05-14 : 09:39:07

Thanks! I think this should be it (solution by Peso). It works at least at first sight, I will do some further testing and if it does not work I will get back on this issue.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources