Query layer to show two tables as one

1138
10
Jump to solution
11-02-2021 08:21 AM
DougBrowning
MVP Esteemed Contributor

I am trying to make a query layer that shows two tables in one.  Both tables have the exact same schema.  It is not a join, just want to show the two tables like they are one to the user.  They are in the same SDE.

In SQL I can just do this 

SELECT * FROM [ilPub].[PubDBO].[TERRA], [ilPub].[PubDBO].[LMF]

In ArcMap I get a ambiguous name objectid error.  I have done a ton of searching but only finding joins.

thanks

0 Kudos
1 Solution

Accepted Solutions
ABishop
MVP Regular Contributor

If you want to write a SQL statement to accomplish this without appending the tables, you can use a UNION

https://mode.com/sql-tutorial/sql-union/ 

Amanda Bishop, GISP

View solution in original post

0 Kudos
10 Replies
ABishop
MVP Regular Contributor

Hello @DougBrowning 

Try a relate.

Amanda Bishop, GISP
0 Kudos
DougBrowning
MVP Esteemed Contributor

The two are not related.  Just want to show both.

Table 1

1

2

3

Table 2

7

8

9

Final table

1

2

3

7

8

9

 

Thanks

0 Kudos
ABishop
MVP Regular Contributor

In order to do this you must have a unique ID that you can either join or relate the tables.

Amanda Bishop, GISP
0 Kudos
DougBrowning
MVP Esteemed Contributor

They are not to be joined or related.  I just want to see both at once in the same table.  So if I have 1,000 records in table 1 and 500 in table 2 then the result table has 1,500 rows.

Thanks

0 Kudos
ABishop
MVP Regular Contributor

Do you want them to be permanently this way?  If so, you always append one table to the other.

Amanda Bishop, GISP
0 Kudos
ABishop
MVP Regular Contributor

If you want to write a SQL statement to accomplish this without appending the tables, you can use a UNION

https://mode.com/sql-tutorial/sql-union/ 

Amanda Bishop, GISP
0 Kudos
DougBrowning
MVP Esteemed Contributor

Ok it looks like that worked.  thanks a lot

ABishop
MVP Regular Contributor

You're welcome

Amanda Bishop, GISP
0 Kudos
MapsAreFun
New Contributor II

Hi, just wondering if you can post an example of the union sql you used for this query layer? I have tried the options provide on https://mode.com/sql-tutorial/sql-union/ but I am receiving a "Operation failed" message in ArcGIS Pro.

0 Kudos