topic Re: Query layer to show two tables as one in ArcMap Questions

Query layer to show two tables as one

DougBrowning — Tue, 02 Nov 2021 15:21:20 GMT

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

Re: Query layer to show two tables as one

ABishop — Tue, 02 Nov 2021 15:27:37 GMT

Hello @DougBrowning

Try a relate.

Re: Query layer to show two tables as one

DougBrowning — Tue, 02 Nov 2021 15:42:31 GMT

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

Table 1

Table 2

Final table

Thanks

Re: Query layer to show two tables as one

ABishop — Tue, 02 Nov 2021 16:38:22 GMT

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

Re: Query layer to show two tables as one

DougBrowning — Tue, 02 Nov 2021 16:43:09 GMT

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

Re: Query layer to show two tables as one

ABishop — Tue, 02 Nov 2021 16:44:52 GMT

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

Re: Query layer to show two tables as one

ABishop — Tue, 02 Nov 2021 16:47:01 GMT

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/

Re: Query layer to show two tables as one

DougBrowning — Tue, 02 Nov 2021 17:30:20 GMT

Ok it looks like that worked. thanks a lot

Re: Query layer to show two tables as one

ABishop — Tue, 02 Nov 2021 17:46:07 GMT

You're welcome

Re: Query layer to show two tables as one

MapsAreFun — Mon, 17 Jul 2023 07:36:18 GMT

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.

Re: Query layer to show two tables as one

DougBrowning — Mon, 17 Jul 2023 22:35:58 GMT

Just straight union if the schemas are the same, and they must be. If not add a field list to make them the same. Also it must have a ObjectID field. If versioned make sure to use the views.

select * from Pub.PubDBO.Terr_evw UNION ALL select * from Pub.PubDBO.LMF_evw

hope that helps