Problem with SQL UNION ALL query

[Ramshan]

I am having two tables dbo_hpr and dbo_bridges. Only some of the columns in dbo_hpr are matching with dbo_bridges, SQL UNION ALL query only brings up the values that are common in both the tables. But I need to show all the values from both the table. So how shall I write the query to bring all the values from 2 tables?

dbo_hpr contains: dbo_hpr.OBJECTID, dbo_hpr.PROPNAME, dbo_hpr.RESNAME, dbo_hpr.ADDRESS, dbo_hpr.CITY, dbo_hpr.VICINTIY, dbo_hpr.COUNTYCD, dbo_hpr.LOT, dbo_hpr.BLOCK, dbo_hpr.PLATNAME, dbo_hpr.SECTION, dbo_hpr.TOWNSHIP, dbo_hpr.RANGE, dbo_hpr.RESTYPE, dbo_hpr.HIST_FUNC, dbo_hpr.CURR_FUNC, dbo_hpr.AREASG_1, dbo_hpr.AREASG_2, dbo_hpr.desc_seg, dbo_hpr.DOC_SOURCE, dbo_hpr.NAME_PREP, dbo_hpr.SURVEY_PRO, dbo_hpr.Projectname, dbo_hpr.DATE_PREP, dbo_hpr.PHOTOGRAPH, dbo_hpr.Year, dbo_hpr.ARCH_BUILD, dbo_hpr.YEAR_BUILD, dbo_hpr.ORIG_SITE, dbo_hpr.DATEMOVED, dbo_hpr.FROMWHERE, dbo_hpr.ACCESSIBLE, dbo_hpr.ARCH_STYLE, dbo_hpr.OTHER_ARCH, dbo_hpr.FOUN_MAT, dbo_hpr.ROOF_TYPE, dbo_hpr.ROOF_MAT, dbo_hpr.WALL_MAT_1, dbo_hpr.WALL_MAT_2, dbo_hpr.WINDOW_TYP, dbo_hpr.WINDOW_MAT, dbo_hpr.DOOR_TYP, dbo_hpr.DOOR_MAT, dbo_hpr.EXTER_FEA, dbo_hpr.INTER_FEA, dbo_hpr.DEC_DETAIL, dbo_hpr.CONDITION, dbo_hpr.DES_RES, dbo_hpr.COMMENTS, dbo_hpr.PLACEMENT, dbo_hpr.lonr, dbo_hpr.CONTINUATION, dbo_hpr.NRData, dbo_hpr.Date_Updated, dbo_hpr.Lat, dbo_hpr.Long, dbo_hpr.UTM_Zone, dbo_hpr.Easting, dbo_hpr.Northing, dbo_hpr.P_B_C, dbo_hpr.Year_Closed

Dbo_bridges contains: Dbo_Bridges1.F_Main_Div, Dbo_Bridges1.FHWA_No, Dbo_Bridges1.CITY, Dbo_Bridges1.ADDRESS, Dbo_Bridges1.Feat_Int, Dbo_Bridges1.Prog_Res_Date, Dbo_Bridges1.Rep_Proj_No, Dbo_Bridges1.Struct_No, Dbo_Bridges1.Date_Built, Dbo_Bridges1.Designer, Dbo_Bridges1.Builder, Dbo_Bridges1.Bridge_Plate, Dbo_Bridges1.Hist_Events, Dbo_Bridges1.Jurisdiction, Dbo_Bridges1.Struct_Use, Dbo_Bridges1.Traffic_Open, Dbo_Bridges1.Traffic_Close, Dbo_Bridges1.Main_Struct_Typ, Dbo_Bridges1.Design_Config, Dbo_Bridges1.No_Spans, Dbo_Bridges1.Struct_Len, Dbo_Bridges1.Span_Type1, Dbo_Bridges1.Span_Type2, Dbo_Bridges1.Span_Type3, Dbo_Bridges1.Span_Type4, Dbo_Bridges1.Span_Type5, Dbo_Bridges1.Span_Len1, Dbo_Bridges1.Span_Len2, Dbo_Bridges1.Span_Len3, Dbo_Bridges1.Span_Len4, Dbo_Bridges1.Span_Len5, Dbo_Bridges1.Lanes_Struct, Dbo_Bridges1.Struct_width, Dbo_Bridges1.Dec_Arch_Fea, Dbo_Bridges1.Piers, Dbo_Bridges1.Abutments, Dbo_Bridges1.Wings, Dbo_Bridges1.Seats, Dbo_Bridges1.Material, Dbo_Bridges1.Source, Dbo_Bridges1.Connections, Dbo_Bridges1.Top_Chord, Dbo_Bridges1.End_Posts, Dbo_Bridges1.Btm_Chord, Dbo_Bridges1.Posts, Dbo_Bridges1.Diagonal, Dbo_Bridges1.Counters, Dbo_Bridges1.Mis_Info, Dbo_Bridges1.Photo_No, Dbo_Bridges1.Construct_Plan, Dbo_Bridges1.Location, Dbo_Bridges1.Other_D_Config, Dbo_Bridges1.Recorder, Dbo_Bridges1.Date, Dbo_Bridges1.Title, Dbo_Bridges1.P_B_C, Dbo_Bridges1.COUNTYCD, Dbo_Bridges1.Plat_Name, Dbo_Bridges1.Section, Dbo_Bridges1.Township, Dbo_Bridges1.Range, Dbo_Bridges1.Latitude, Dbo_Bridges1.Longitude, Dbo_Bridges1.UTMZone, Dbo_Bridges1.Northings, Dbo_Bridges1.Eastings, Dbo_Bridges1.LONR, Dbo_Bridges1.NRData

 

[pbaldy]

If you mean fields, you alias the field for the table that doesn't have it:

SELECT Field1, Field2
FROM Table1
UNION ALL
SELECT Field1, "None" As Field2
FROM Table2

You can use Null, 0, "" or whatever is appropriate to your needs.

 

[jdraw]

Each SQL statement within the UNION query must have the same number of fields in the result sets with similar data types.

What is it you are really trying to accomplish?

With fields like this

Dbo_Bridges1.Span_Type1, Dbo_Bridges1.Span_Type2, Dbo_Bridges1.Span_Type3, Dbo_Bridges1.Span_Type4, Dbo_Bridges1.Span_Type5,

it certainly appears that you should do some Normalization on your tables.

 

[Ramshan]

Two tables are having some 55 fields each and some 6 in common. I am using this 6 as the input criteria in a query form to bring up all the other fields from both the tables. This is my main aim.

 

[jdraw]

As I reread your post, I think you are looking for something like

Select table1.*, Table2.*
from Table1, Table2
Where
Table1.fldA = table2.fldA and
Table1.fldB = table2.FldB

..... where fldA, fldB... are the fields these tables have in common. But I'm really just guessing at what

to bring up all the other fields from both the tables

means.