Combining 3 tables in one query (1 Viewer)

[Maike]

Good Morning,

apologies, my SQL query skills aren't what they used to be and I'm stuck.

I have two linked tables: FPNA Data (Exel) and vwMaster (SQL server) with a variety of information all linked by a common field. I use a left join query that takes all the records from FPNA Data and adds the matching information from vwMaster and it looks like so:

SELECT DISTINCT [FPNA Data].InnCode, [FPNA Data].FacilityId, vwmaster.ShortName, vwmaster.HotelName, vwmaster.Capacity, [FPNA Data].Active, [FPNA Data].PropPlanUnit, [FPNA Data].BU_OU, [FPNA Data].[Planning Entity], [FPNA Data].[Planning Product], [FPNA Data].CompCurrentYr, [FPNA Data].CompNextYr, [FPNA Data].RFD, vwmaster.AVP, vwmaster.SVP, [FPNA Data].[Sub Area], [FPNA Data].Subregion, [FPNA Data].MajorMarket, [FPNA Data].Owner, [FPNA Data].[Asset Manager], vwmaster.BrandCode, vwmaster.BrandDivision, vwmaster.FacilityType, vwmaster.LocationType, [FPNA Data].VPFinance, [FPNA Data].DOF, [FPNA Data].[DOF Email Address], [FPNA Data].ADOF, [FPNA Data].[ADOF Email Address], vwmaster.GM, vwmaster.CTYHOCN, vwmaster.City, vwmaster.STATE, vwmaster.Country, vwmaster.ChainScale, vwmaster.CurrencyCode, vwmaster.Market, [FPNA Data].Top, [FPNA Data].Engineering, [FPNA Data].[F&B], [FPNA Data].Housekeeping, [FPNA Data].EssbaseName, [FPNA Data].Hotel_Status_COVID19, [FPNA Data].Suspension_Date, [FPNA Data].ReOpen_Date, [FPNA Data].[Comp 2018], [FPNA Data].[Comp 2019]
FROM [FPNA Data] LEFT JOIN vwmaster ON [FPNA Data].[FacilityId] = vwmaster.[FacilityID]
WHERE ((([FPNA Data].Active)="Active" Or ([FPNA Data].Active)="2020 transition" Or ([FPNA Data].Active)="2021 opening" Or ([FPNA Data].Active)="2021 transition"))

Now I have another linked table called Rollups. It essentially contains all the records that aren't in the other two sources. It only has 3 fields:
ShortName
Active
PropPlanUnit

I need to add all records from Rollups to the bottom of the query above, but I don't know how to do that My thought is to just populate the 3 fields that exist and leave all the other's blank because there is no matching data in FPNA and vwMaster.

Help please.

Cheers
M.

 

[The_Doc_Man]

As long as you are willing to be a bit tedious during the creation of this query, and as long as you were not planning to do updates through it, you could try a UNION query and supply constants (0 or "" as needed) to match the fields in question. You can union a query to a table if needed, SQL doesn't care. However, put the ROLLUPS table as the 2nd member in the query because the first member supplies field names.

 

[theDBguy]

Hi. Welcome to AWF!

I agree. If you're trying to stack up the data, then a UNION query will probably do it for you.

 

[Maike]

This is supposed to update dynamically. The query feeds a bunch of reporting tools and serves as entity list. So as stuff is added to what feeds my tables, the query is supposed to spit out the list without further manual intervention. I don't know how UNION works. Googled it and tried adding it to the above, but I ended up with 4m records instead of 421 xD

 

[The_Doc_Man]

Show us the UNION query that give you 4m records. Probably will be a simple syntax thing. It SOUNDS like either the rollup or the UNION itself was constructed to do combinatorial matching, giving you all possible combinations of the records in the two sets.