Multiple table queries

SELECT CUSTOMER.CustABN, CustName, InvoiceNo
FROM CUSTOMER, INVOICE
WHERE CUSTOMER.CustABN = INVOICE.CustABN;

Notice in the query we had to declare which table we wanted the CustABN to come from, and in the WHERE statement declare the fields in both tables.

This is because there is ambiguity, as CustABN exists in both tables. We must specify which table SQL Plus should pick the field from. This is only a problem when we have the same field names (notice we did not worry about CustName and InvoiceNo).

Remember that we can also uses aliases for our field names. We can also do the same for our tables. If the above query was a more involved one we would soon get sick of typing CUSTOMER.Something or INVOICE.Something.

We can make life easier with something like this:

SELECT CUSTOMER.CustABN AS ABN, CustName AS Name, InvoiceNo AS "Invoice Number"
FROM CUSTOMER c, INVOICE i
WHERE c.CustABN = i.CustABN;

This returns the same but with meaningful column headers. Note the use of double quotes as there is a space in the string.      

SELECT DISTINCT c.CustABN AS ABN, CustName AS Name
FROM CUSTOMER c, INVOICE i
WHERE c.CustABN = i.CustABN
ORDER BY CustName;

SELECT c.CustABN, CustName, inv.InvoiceNo, Quantity, init.ItemNo, ItemDesc, ItemPrice
FROM CUSTOMER c, INVOICE inv, INVOICEITEM init, ITEM it
WHERE c.CustABN = inv.CustABN
AND init.InvoiceNo = inv.InvoiceNo
AND it.ItemNo = init.ItemNo
AND inv.InvoiceNo = 21;

SELECT CustABN, COUNT(InvoiceNo)
FROM INVOICE
GROUP BY CustABN;

As we want to display the name of the customers instead of the ABN, we need to join the CUSTOMER and the INVOICE tables. The linking field for the two tables is CustABN.

SELECT c.CustName, COUNT(i.InvoiceNo)
FROM CUSTOMER c, INVOICE i
WHERE c.CustABN = i.CustABN
GROUP BY c.CustName;

We could also use aliases for CustName and COUNT(i.InvoiceNo)

SELECT SUM(Quantity * ItemPrice) as InvTotal, CustName, inv.InvoiceNo
FROM InvoiceItem ii, Item i, Invoice inv, Customer c
WHERE ii.ItemNo = i.ItemNo
AND inv.InvoiceNo = ii.InvoiceNo
AND inv.CustABN = c.CustABN
GROUP BY inv.InvoiceNo, CustName;


Without the HAVING clause:

SELECT c.CustName, COUNT(i.InvoiceNo)
FROM CUSTOMER c, INVOICE i
WHERE c.CustABN = i.CustABN
AND c.CustName < 'F'
GROUP BY c.CustName;

With the HAVING clause:

SELECT c.CustName, COUNT(i.InvoiceNo)
FROM CUSTOMER c, INVOICE i
WHERE c.CustABN = i.CustABN
GROUP BY c.CustName
HAVING c.CustName < 'F';

SELECT c.CustName, COUNT(i.InvoiceNo)
FROM CUSTOMER c, INVOICE i
WHERE c.CustABN = i.CustABN
GROUP BY c.CustName
HAVING COUNT(i.InvoiceNo) > 1;

Would this query work?

SELECT c.CustName, COUNT(i.InvoiceNo)
FROM CUSTOMER c, INVOICE i
WHERE c.CustABN = i.CustABN
AND COUNT(i.InvoiceNo) > 1
GROUP BY c.CustName;

WHERE works on individual rows - only rows that satisfy the condition(s) are included. HAVING works on groups - only groups that satisfy the condition(s) are included. Thus WHERE conditions cannot include grouping functions and HAVING cannot include conditions that operate at the row and not the group level.

SELECT c.CustName, COUNT(i.InvoiceNo)
FROM CUSTOMER c, INVOICE i
WHERE c.CustABN = i.CustABN
AND (c.CustName LIKE '%t%' OR c.CustName LIKE '%T%')
GROUP BY c.CustName;

Note the use of the ( )s. This ensures that the only rows included satisfy:

• the join condition (c.CustABN = i.CustABN), and
• have a 't' in their name (c.CustName LIKE '%t%' OR c.CustName LIKE '%T%')

Without the ( )s, namely:

SELECT c.CustName, COUNT(i.InvoiceNo)
FROM CUSTOMER c, INVOICE i
WHERE c.CustABN = i.CustABN
AND c.CustName LIKE '%t%' OR c.CustName LIKE '%T%'
GROUP BY c.CustName;

The WHERE clause is interpreted as rows that satisfy either:

• the first condition (c.CustABN = i.CustABN AND c.CustName LIKE '%t%'), or
• the second condition (c.CustName LIKE '%T%').

This gives a rubbish answer.

Remember AND has higher precedence than OR. So use ( )s in statements that have both AND and OR conditions.

SELECT Student.Name
FROM Student, Coordinator
WHERE Student.Coord_No=Coordinator.Coord_No
AND Coordinator.Name = 'Fred Flinstone';

Note that for this query all the columns had to be qualified (e.g. Student.Name). The columns by themselves (e.g. Name or Coord_No) would have been ambiguous as they appeared in both tables. If the column name is unique in all tables involved in the query it need not be qualified e.g. the columns Employee_No or Total_Units are unique across both Student and Coordinator.

SELECT Student.Name
FROM Student, Course, Grade
WHERE Student.Student_No=Grade.Student_No
AND Course.Course_No = Grade.Course_No
AND Course.Name = 'Project';

SELECT c.Name, AVG(Total_Units)
FROM Student s, Coordinator c
WHERE s.Coord_No = c. Coord_No
AND Employee_No = 'AS1234'
GROUP BY c.name;

Note that with this example the tables are joined together and the conditions calculated (the effect of the WHERE and AND clauses are computed) before the grouping and aggregations functions are applied.

SELECT c.Name, AVG(Total_Units)
FROM Student s, Coordinator c
WHERE s.Coord_No = c. Coord_No
AND Employee_No LIKE 'AS%'
GROUP BY c.name;

Or another alternate way to write the query would be:

SELECT c.Name, AVG(Total_Units)
FROM Student s, Coordinator c
WHERE s.Coord_No = c. Coord_No
GROUP BY c.name, Employee_No
HAVING Employee_No LIKE 'AS%';

Note to use the Employee_No column in the HAVING clause it needs to appear in the GROUP BY clause.