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 may be occasions where there is a need to merge data from two or more tables and rename the column names in the output.
One such business scenario which may arise, is if there is a need to send an email broadcast to both customers and staff alike. This could be an announcement that you would like to share with your customers, but you need to copy in to your sales staff so that they can also read the message being sent to customers.
This can be achieved by using the UNION function, which can merge the output from two or more queries.
Below is the result of this live query:
| salutation | firstname | surname | |
|---|---|---|---|
| Mrs | Anthea | Adams | atraders@hotmailtest.com |
| Mr | Bertie | Billingham | bertie@billinghamstest.co.uk |
| Mr | Cecil | Collins | colcon@gmailtest.com |
| Miss | Diana | Dimbleby | dimblebyplc@talktalktest.co.uk |
| Ms | Elaine | East | sales@eastempiretest.com |
| Mr | Frederick | Franklin | fred@frannockstest.com |
| Mr | George | Gimley | georgegimley@aoltest.com |
| Miss | Hannah | Hamshaw | hh@hamshawltdtest.com |
| Mrs | Irene | Ingols | iingols@gmailtest.com |
| Mr | Jamie | Jedson | jamiej@jedsonstest.com |
| Mr | Ken | Kendrick | kkendrick@kimboltoncarrierstest.co.uk |
| Mr | Liam | Lee | llee@lemmystest.com |
| Miss | Miranda | Mulgroon | miranda@mulgroonstest.co.uk |
| Mrs | Norma | Nixon | nnixon@normascakestest.com |
| Mr | Oliver | Oswald | osfurnishings@yahootest.co.uk |
| Mr | Peter | Peacock | peterp@hotmailtest.com |
| Mr | Quentin | Quinn | qquinn@talktalktest.co.uk |
| Mrs | Rena | Rimshaw | rrimshaw@renastest.com |
| Mr | Simon | Sixsmith | simon.sixsmith@shenleysshovelstest.co.uk |
| Mr | Tim | Timpkins | t.timpkins@tmltdtest.co.uk |
| Ms | Una | Unsworth | uunsworth@aoltest.com |
| Mr | Vinnie | Venga | vinnie.venga@yahootest.co.uk |
| Mr | Will | Wilton | will@wiltonstest.com |
| Mrs | Xena | Xavier | xena@xavierctoctest.com |
| Miss | Yasmine | Yolander | yasmine@yaxleypiestest.co.uk |
| Mr | Zak | Zanetti | zakzanetti@hotmailtest.com |
| Mr | Arnold | Ambleton | aa@hotmailtest.com |
| Mr | Bertie | Beckley | bb@beckleytest.com |
| Mr | Cedric | Collier | cedric@collierstest.com |
| Mr | Derek | Duke | theboss@dukestest.co.uk |
| Mr | Eddie | Emmerson | eddie@emmersonstest.com |
| Miss | Fiona | Finley | ff@fenthamfarmstest.net |
| Mr | Garry | Gumton | gary@gurnockstest.com |
| Mr | Henry | Hammerton | henry@hammertontest.co.uk |
| Mr | Ian | Icke | ii@irlingtonltdtest.com |
| Mr | Jeff | Johnston | mrjohnston@johnstontest.com |
| Mrs | Karen | Kensome | kk@kandktest.com |
| Mrs | Lena | Lilly | lenalilly@locallandmarkstest.com |
| Mr | Matthew | Marsh | matt@motormantest.com |
| Ms | Noreen | Nightingale | nn@noreennightingaletest.com |
| Mr | Orville | Owen | orville@oakham-optometriststest.com |
| Mr | Paul | Purvis | pp@purvisandpurvistest.com |
| Mr | Quincy | Quant | quincy@quakermanstest.com |
| Mr | Roger | Renley | roger@renleystest.com |
| Mrs | Sally | Smithers | sally@smitherstest.com |
| Mr | Toby | Thompson | tthompson@hotmailtest.com |
| Mrs | Ursula | Umbridge | sales@umbridgetest.com |
| Mrs | Violet | Vardy | vv@violetvanstest.net |
| Mr | Wayne | Williams | wayne@wwtest.co.uk |
| Mrs | Xaria | Xanda | xx@xceltest.com |
| Mr | Yusef | Yorke | yusef@yeovilyardstest.com |
| Mr | Zane | Zellweger | zane@zenstest.com |
| Mrs | Anthea | Allen | a.allen@widge-it.com |
| Mr | Billy | Beaumont | b.beaumont@widge-it.com |
| Mr | Charles | Chuzzlewit | c.chuzzlewit@widge-it.com |
| Miss | Deirdrie | Dawson | d.dawson@widge-it.com |
| Mr | Ernie | Ecclestone | e.ecclestone@widge-it.com |
| Mrs | Francesca | Faversham | f.faversham@widge-it.com |
| Miss | Gina | Ginola | g.ginola@widge-it.com |
| Mr | Harry | Harrison | h.harrison@widge-it.com |
Code for both MySQL and T-SQL (MS SQL)
SELECT cust_salutation AS salutation ,cust_firstname AS firstname, cust_surname AS surname, cust_email AS email
FROM customer
UNION
SELECT staff_salutation, staff_firstname, staff_surname, staff_email
FROM staff
Click the button below to advance to the next stage
Or use the above 'SQL Queries' dropdown menu to jump to any stage.