Can anyone tell me what is wrong with this statement? SELECT invoices.invoicenum
ID: 3772478 • Letter: C
Question
Can anyone tell me what is wrong with this statement?
SELECT invoices.invoicenumber AS [Invoice Number], InvoiceLineItems.InvoiceLineItemDescription AS Description, vendors.VendorName AS Vendor,
InvoiceLineItems.InvoiceLineItemAmount AS amount, invoices.InvoiceDate AS [Invoice Date]
FROM invoices, InvoiceLineItems, vendors
FULL OUTER JOIN invoices ON invoices = InvoiceLineItems
FULL OUTER JOIN InvoiceLineItems ON InvoiceLineItems = vendors
WHERE invoices.invoicenumber = 'QP58872' OR invoices.invoicenumber = 'Q545443' OR invoices.invoicenumber = 'MABO1489' ;
The help i got was "When you run it you get errors on invalid column name because you are trying to join on a table, not a column."
Thanks
Explanation / Answer
Given table names
1.invoices
2.InvoiceLineItems
3.vendors
invoices alias name ( in)
InvoiceLineItems alias name( IL)
vendors alias name (v)
Query
select in.invoicenumber||'~'||in.InvoiceDate||'~'||IL.InvoiceLineItemDescription||'~'||IL.InvoiceLineItemAmount||'~'||v.VendorName
from
(invoices in FULL OUTER JOIN InvoiceLineItems IL on in.invoicenumber=IL.invoicenumber
FULL OUTER JOIN vendors v on ISNULL(in.invoicenumber,IL.invoicenumber)=v.invoicenumber)
where in.invoicenumber in('QP58872','Q545443','MABO1489');