The Query on the left is "Customers" and all records will be listed with records from catalogsales being listed only if there is a match.
To do this it will be necessary to use an Outer Join Query. The objective of the following example is to view a list of all customers regardless of whether or not they have ordered from us and if they have, then return the dollar value of those orders. Sqlite> SELECT Customers.AcctNumber,Customers.Custname,catalogsales.InvoiceNo,SUM(Price*Quan) 'TOTAL' FROM Customers,catalogsales WHERE Customers.AcctNumber=catalogsales.AcctNumber GROUP BY catalogsales.AcctNumber Ī left outer join returns all the records from the table on the left side of the JOIN clause and only those records from the table on the right that match the specified criteria. The invoice charges for each account will be added together yielding three rows since there were three customers that were invoiced. If we group by Account Number then we get a slightly different result. Sqlite> SELECT Customers.AcctNumber,Customers.Custname ,catalogsales.InvoiceNo ,SUM(Price*Quan) 'TOTAL' FROM Customers ,catalogsales WHERE Customers.AcctNumber= catalogsales.AcctNumber GROUP BY InvoiceNo In the example below we are grouping by invoice number (InvoiceNo), since there are 4 distinct invoice numbers there are four line items in the query result If you want a query with an aggregate function to deliver multiple rows such as by invoice number or account number then you must use the GROUP BY clause and the appropriate column to group on. All the line items were added together and incorrectly attributed to one customer. Sqlite> SELECT Customers.AcctNumber,Customers.Custname ,catalogsales.InvoiceNo, SUM(Price*Quan) 'TOTAL' FROM Customers ,catalogsales WHERE Customers.AcctNumber= catalogsales.AcctNumber Well we could try to use the SUM aggregate function as we did to calculate the total value of the inventory. Let us say that we want to know what each customer spent. Sqlite> SELECT Customers.AcctNumber,Customers.Custname ,catalogsales.InvoiceNo,ItemNo,Price,Quan ,(Price*Quan) 'EXT' FROM Customers ,catalogsales WHERE Customers.AcctNumber= catalogsales.AcctNumber Be very careful in using Natural Join queries in the absence of properly matched columns, a cartesian product will be produced. AcctNumber Ī NATURAL JOIN will also work in the above example since the primary key and the foreign key in the two tables have the same name.
SQLITE INNER JOIN EXAMPLE CODE
The following code will achieve the same result. The above query by the way is known as in Inner Join query where the only rows returned are ones in which both tables have fields that match the stated criteria. Sqlite> SELECT DISTINCT Customers.AcctNumber, Customers.Custname FROM Customers,Ĭust_invoice WHERE Customers.AcctNumber = cust_invoice.AcctNumber Using the "SELECT DISTINCT" clause as shown below, enables us to eliminate the duplicate rows. However we are only interested in just a list of customers that have ordered from us. If they had 50 invoice numbers under the same account number then they would be listed 50 times. That they are listed in the results twice. has two invoice numbers associated with the same account number in the "cust_invoice" table Sqlite> SELECT Customers.AcctNumber, Customers.Custname FROM Customers, cust_invoice WHERE Customers.AcctNumber = cust_invoice.AcctNumber In the Customers table with the foreign key field "AcctNumber" in the cust_invoice table To find this out we can relate the table listing the customer accounts with the table listing the invoices by matching the primary key field "AcctNumber" Let us say that we want a list of customers that have ordered from us in the past. "catalogsales" which is a detail table for "cust_invoice" listing the individual items ordered by our customers on each invoice using the invoice number "InvoiceNo" as a foreign key. Here we have created three tables, one a list of customers and their Account numbers called oddly enough, "Customers"Ī table called "cust_invoice" listing invoices and using the customer account number as a foreign key with the "Customers" table.
0 kommentar(er)
