Can anyone tell me what is wrong with this statement? SELECT invoices.invoicenum

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');

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.