Question

Hi this is for SQL

Use the AP database

Vendor name from the vendor table

Vendor Contact First Name and Vendor Contact Last Name from the vendor table but I need them concatenated together with a space between the names

Invoice Date from the invoice table

Only the day of from the Invoice Date

Invoice Number from the invoice table

The third and fourth characters from the Invoice Number

Line Item Amount from the invoice line items table

Calculate the commission which is the Line Item Amount * 15% and make it ONLY two decimals

The FROM is the Vendors table do JOINS on the others

The WHERE is where the Line Item Amount is greater than $2000 and the Invoice Date is between July 1, 2014 and July 31st, 2014 and I only want the ones that have either a 9 or a 2 in the Invoice Number

Sort by Invoice Date

My answer like this:

elect vendor_name as vendor, concat(first_name, ' ', last_name) as Contact, DAY(invoice_date) as ofday, invoice_date,
SUBSTRING(invoice_number, 3,2) as thirdFourth, line_item_amount,round(line_item_amount*.15, 2) as Commission
from vendors v
JOIN vendor_contacts vc
ON v.vendor_id = vc.vendor_id
JOIN INVOICES INV
ON V.VENDOR_ID=INV.VENDOR_ID
JOIN invoice_line_items ILI
ON INV.invoice_number=ILI.invoice_number
where line_item_amount > 2000
and invoice_date between '2014-7-1' and '2014-7-31'
and (invoice_number =9 OR invoice_number =2)
order by Invoice_Date

I am getting this err

Error Code: 1054. Unknown column 'ILI.invoice_number' in 'on clause

Answer 1

If you have any doubts/errors, please give me comment...

Please check once whether invoice_line_items table has invoice_number column exists or not, it is a foreign key which is referencing INVOICE table, otherwise please provide screenshot of invoice_line_items table schema

SELECT vendor_name as vendor, concat(first_name, ' ', last_name) as Contact, DAY(invoice_date) as ofday, invoice_date, SUBSTRING(invoice_number, 3,2) as thirdFourth, line_item_amount,round(line_item_amount*.15, 2) as Commission

FROM vendors v JOIN vendor_contacts vc ON v.vendor_id = vc.vendor_id

JOIN INVOICES INV ON V.VENDOR_ID=INV.VENDOR_ID JOIN invoice_line_items ILI ON INV.invoice_number=ILI.invoice_number

WHERE line_item_amount > 2000 and invoice_date between '2014-7-1' and '2014-7-31' and (INV.invoice_number =9 OR INV.invoice_number =2)

ORDER BY Invoice_Date;