The purpose of this set of web pages is to demonstrate knowledge of building Microsoft Excel based solutions, data structure design and live querying on data tables (MySQL and MS SQL).
There are instances where information will need to be extracted from a joined set of data tables where keys do not match. The following example demonstrates an SQL query to find customers who have not placed a sales order this year.
An inner join would only return data on customers that have corresponding data in the Transactions table. However, in this scenario there will be no matching data in the Transactions table as no data exists.
The solution is an outer join query as this type of query will return all customer data, even if it doesn't match data in the Transactions table. Here a left join has been performed, but an extra clause has been added to only return customer data where the trans_cust_id is null. Thus picking up those companies that have not placed an order:
| cust_name | cust_fullname | trans_grn_widgets_sales | trans_blu_widgets_sales |
|---|---|---|---|
| Colcon Ltd | Mr Cecil Collins | ||
| Hamshaw Ltd | Miss Hannah Hamshaw | ||
| Immingham Supplies | Mrs Irene Ingols | ||
| Jedson Manufacturing Ltd | Mr Jamie Jedson | ||
| Kimbolton Carriers Ltd | Mr Ken Kendrick | ||
| Lemmy's Wines and Spirits Ltd | Mr Liam Lee | ||
| Mulgroon's Balloons Ltd | Miss Miranda Mulgroon | ||
| Norma's Cakes Ltd | Mrs Norma Nixon | ||
| Oswald Furnishings Ltd | Mr Oliver Oswald | ||
| Tyneside Machines Ltd | Mr Tim Timpkins | ||
| Venga Coverings Ltd | Mr Vinnie Venga | ||
| Xavier Coast to Coast Ltd | Mrs Xena Xavier | ||
| Ambletons PLC | Mr Arnold Ambleton | ||
| Beckley Banners Ltd | Mr Bertie Beckley | ||
| Cedric Collier Ltd | Mr Cedric Collier | ||
| Duke Demolition Ltd | Mr Derek Duke | ||
| Emmerson Enterprises Ltd | Mr Eddie Emmerson | ||
| Fentham Farms Ltd | Miss Fiona Finley | ||
| Gurnock Garage Ltd | Mr Garry Gumton | ||
| Henry Hammerton Ltd | Mr Henry Hammerton | ||
| Local Landmarks Ltd | Mrs Lena Lilly | ||
| Motorman Ltd | Mr Matthew Marsh | ||
| Noreen Nightingale Ltd | Ms Noreen Nightingale | ||
| Oakham Optometrists Ltd | Mr Orville Owen | ||
| Purvis & Purvis Ltd | Mr Paul Purvis | ||
| Quakerman's Quills Ltd | Mr Quincy Quant | ||
| Renley of Roehampton Ltd | Mr Roger Renley | ||
| Williams of Walsham Ltd | Mr Wayne Williams |
MySQL Code
SELECT c.cust_name, CONCAT(c.cust_salutation, ' ',c.cust_firstname,' ',c.cust_surname) AS cust_fullname,
t.trans_grn_widgets_sales, t.trans_blu_widgets_sales
FROM customer AS c
LEFT JOIN transactions AS t
ON c.cust_id = t.trans_cust_id
WHERE t.trans_cust_id IS NULL
T-SQL (MS SQL)
SELECT c.cust_name,
c.cust_salutation + ' ' + c.cust_firstname + ' ' + c.cust_surname AS cust_fullname,
t.trans_grn_widgets_sales, t.trans_blu_widgets_sales
FROM customer AS c
LEFT OUTER JOIN transactions AS t
ON c.cust_id = t.trans_cust_id
WHERE t.trans_cust_id IS NULL
Click the button below to advance to the next stage
Or use the above 'SQL Queries' dropdown menu to jump to any stage.