I'm assuming you want to display all employees multiple times with the training documents they have not done yet. SELECT a.lName, a.fName, b.docNumber, b.docNameFROM(SELECT e.lName, e.fName, t.TIDFROM employees eLEFT JOIN trainingRecords t ON e.empID = t.empID) AS a,(SELECT m.docNumber, m.docName, t.TIDFROM masterList mLEFT JOIN trainingRecords t ON m.TID = t.TID) AS bWHERE a.TID IS NULL OR b.TID IS NULLORDER BY a.lName, b.docNumberexample results: lName fName docNumber docNameSimpson Homer 1 Nuclear Physics for DummiesSimpson Homer 2 Nuclear Physics for BeginnersSimpson Homer 3 Advanced Nuclear PhysicsSimpson Lisa 3 Advanced Nuclear Physics. Okay, you have to JOIN all three tables with the trainingRecords table in the middle because it has the columns necessary to link the other two tables.
Your query will look something like this: SELECT E.lName, E.fName, ML.docName, ML.docNumber FROM(employees E LEFT OUTER JOIN trainingRecords TR ON E.empID = TR.empID)RIGHT OUTER JOIN masterList ML ON ML.TID = TR.TIDWHERE TR.TID IS NULLWhat's happening here?First, you're doing a LEFT OUTER JOIN of employees and trainingRecords. The LEFT OUTER is to ensure that all the records from employees show up even if there's no match in trainingRecords (which of course don't exist since trainingRecords has no data at all).Then, you're taking the results of that query and RIGHT OUTER JOINing them to masterList. The RIGHT OUTER guarantees that all masterList records will be included even if there are no matches in trainingRecords.Finally, WHERE TR.TID IS NULL filters out any records that actually matched any (future) records in trainingRecords.
Is it possible to grab data from two tables (that have the same fields) into one view. Basically, so the view sees the data as if it was one table. Data from two tables into one view. Ask Question Asked 9 years, 2 months ago. Adding columns to table1 and table2 after view creation will not show up in viewname. You will need to re-run.