Week 3 notes: Multitable queries

USE ap;

/*
Return the names of vendors from Vendors table and the terms from the Terms table,
associated with one another.
*/
SELECT
    vendor_name AS Vendor, terms_description AS 'Default Terms'
FROM
    vendors
        JOIN
    terms ON default_terms_id = terms_id
ORDER BY terms_description, vendor_name;

SELECT
    vendor_name AS Vendor, terms_description AS 'Default Terms'
FROM
    vendors
        JOIN
    terms ON default_terms_id = terms_id
ORDER BY 'Default Terms', Vendor;

SELECT
    vendor_name AS Vendor, invoice_date AS "Date", invoice_number
FROM
    vendors JOIN invoices
    USING(vendor_id)
    WHERE invoice_date LIKE "2014-06%" OR invoice_date LIKE "2014-07%"
            ORDER BY invoice_date DESC, Vendor;

# Ambiguous column names;
# Disambiguate by referring to their respective tables.
SELECT
    vendor_name AS Vendor, invoice_date AS "Date", invoice_number, vendors.vendor_id
FROM
    vendors JOIN invoices
    ON vendors.vendor_id = invoices.vendor_id
    WHERE invoice_date LIKE "2014-06%" OR invoice_date LIKE "2014-07%"
            ORDER BY invoice_date DESC, Vendor;

# This example uses table aliases (v and i in this example, for Vendors and Invoices,
# respectively).
SELECT
    vendor_name AS Vendor, invoice_date AS "Date", invoice_number AS "Number", v.vendor_id AS "Vendor ID"
FROM
    vendors v JOIN invoices i
    ON v.vendor_id = i.vendor_id
    WHERE invoice_date LIKE "2014-06%" OR invoice_date LIKE "2014-07%"
            ORDER BY invoice_date DESC, Vendor;

# Implicit inner join syntax: deprecated.
SELECT invoice_number, credit_total, terms_description
FROM invoices i, terms t
WHERE i.terms_id = t.terms_id;

# Fucking around.
SELECT terms_description, t.terms_id, i.terms_id
FROM terms t
    JOIN invoices i USING(terms_id)
        WHERE i.terms_id > 5;

# Example of Left Outer Join
SELECT vendor_name, account_number, account_description
FROM vendors v
            LEFT JOIN general_ledger_accounts g
                ON v.default_account_number = g.account_number
                WHERE account_description NOT LIKE 'State Corporation Incomes Taxes'
                ORDER BY account_description, vendor_name DESC;

# Example of a self-join.
# One table is aliased so as to treat it like two different tables.
USE ex;
# SELECT fields to work with...
SELECT CONCAT(emps.last_name, ", ", emps.first_name) AS subordinate, CONCAT(mgrs.last_name, ", ", mgrs.first_name) AS boss
# ....FROM employees table....
FROM employees emps
    # ...JOIN mgrs part of employees...
    JOIN employees mgrs
    # ....on the basis of the value of manager_id of employee = employee_id of the manager.
    ON emps.manager_id = mgrs.employee_id;

# 3-table join example.
USE ap;
SELECT DISTINCT vendor_name, line_item_description
FROM vendors
    JOIN invoices USING(vendor_id)
    JOIN invoice_line_items USING(invoice_id)
    WHERE vendor_name NOT LIKE "Postmaster%" OR vendor_name NOT LIKE "United Parcel Service%"
    ORDER BY vendor_name DESC;