SQL query to display rows of 2 different tables

I’m trying to figure out how to pull all rows from two different tables with the OutageID = X. The purpose of this is to view the current outage and all the revisions in one statement to see all of the changes. Below is an example I was trying. However, it puts it all in one row. I want it to display all rows separately like you would if you were to query SELECT * From Table WHERE X = Y.

The Current Outages are in one table and the history is in another so they are not written over and not to change the design of the current DB.

Outages Table

`strSQL = "SELECT Outages.OutageID, Outages.Outage, Outages.Building, 
 Outages.OutageType, Outages.OutageStart, Outages.OutageStartTime, 
Outages.OutageEnd, Outages.OutageEndTime, Outages.Duration, 
Outages.Reason, Outages.Areas, Outages.Comment, Outages.ORN, 
Outages.Contact, Outages.Phone, Outages.Job, Outages.Timestamp
FROM Outages
WHERE (((Outages.OutageID)=3305));"`

Outage History Table

`strSQL = "SELECT OutageHistory.RevisonID, OutageHistory.OutageID, 
  OutageHistory.Outage, OutageHistory.Building, 
   OutageHistory.OutageType, 
   OutageHistory.OutageStart, OutageHistory.OutageStartTime, 
  OutageHistory.OutageEnd, OutageHistory.OutageEndTime, 
  OutageHistory.Duration, OutageHistory.Reason, OutageHistory.Areas, 
 OutageHistory.Comment, OutageHistory.ORN, OutageHistory.Contact, 
 OutageHistory.Phone, OutageHistory.Job, OutageHistory.Timestamp
 FROM OutageHistory
 WHERE (((OutageHistory.OutageID)=3305));"`



`Private Sub All_Revision_Histoy_Click()
Dim strSQL As String

strSQL = "SELECT * From OutageHistory WHERE OutageHistory.OutageID = " & 
Me.OutageID & ";"

Debug.Print strSQL
ShowDataSheet strSQL`

I think that I might need to create a temp table and insert both rows for the results and then Delete the table when its closed. However, I am not sure how to do that. I already feel I may of bitten off more than I can chew with this one. Thank you in advance.

Answer

select * from (
  select 1 as revisionID, Outages.* FROM Outages
    WHERE (((Outages.OutageID)=3305))
  union
  select OutageHistory.* FROM OutageHistory
    WHERE (((OutageHistory.OutageID)=3305))
) order by revisionID desc

Advertisement

Answer