Why do I get multiple values in some columns?

Sometimes a query can return multiple values from a column when only a single value is expected.

Let's use the following query as an example:

SELECT *
  
FROM adw.PropertyFeature pf 
Left Outer Join adw.DimPropertyType dpt on pf.PropertyTypeKey = pf.PropertyTypeKey 
Left Outer Join adw.PropertyGeoData pgd on pgd.[ATTOM ID] = pf.[ATTOM ID] 
Left Outer Join adw.PropertyTax pt on pt.[ATTOM ID] = pf.[ATTOM ID] 
Left Outer Join adw.REOwnership r on r.[ATTOM ID] = pf.[ATTOM ID] 
Left Outer Join adw.DimCounty dc on dc.CountyKey = pf.CountyKey 
Left Outer Join adw.DimJurisdiction dj on dj.JurisdictionKey = pf.JurisdictionKey 

WHERE pf.[ATTOM ID] = 24557391

When we look through the results notice how two different values are shown for latitude and longitude. How can a property have two coordinates?

This is caused because the query involves multiple columns with the same column name. The PropertyFeature table contains columns for Longitude and Latitude that provide the geo coordinates for the property. In addition, the DimCounty table also contains columns for Longitude and Latitude that represent the centroid of the county.

When the SQL Editor included with ATTOM Cloud executes queries against ATTOM Cloud, it will consolidate the values for columns with the same name into a single column. Note: Some editors such as DBeaver will create separate columns in the results. The same holds true for some programming languages (e.g. Python). It is not a good idea to rely on a column name being unique across multiple tables.

Best Practice

When writing queries against ATTOM Cloud that join two or more tables/views it is strongly recommended to assign an alias to each table and view and then to explicitly include both the alias and column name as part of the SELECT statement. This may require a little extra time to write, but it ensures you are getting the specific columns you need from the appropriate tables.views and that the results are unlikely to change in the future if changes are made to the list of columns available as part of any one of the tables/views used in the query.

SELECT   pf.Latitude
	,pf.Longitude
  
FROM adw.PropertyFeature pf 
Left Outer Join adw.DimPropertyType dpt on pf.PropertyTypeKey = pf.PropertyTypeKey 
Left Outer Join adw.PropertyGeoData pgd on pgd.[ATTOM ID] = pf.[ATTOM ID] 
Left Outer Join adw.PropertyTax pt on pt.[ATTOM ID] = pf.[ATTOM ID] 
Left Outer Join adw.REOwnership r on r.[ATTOM ID] = pf.[ATTOM ID] 
Left Outer Join adw.DimCounty dc on dc.CountyKey = pf.CountyKey 
Left Outer Join adw.DimJurisdiction dj on dj.JurisdictionKey = pf.JurisdictionKey 

WHERE pf.[ATTOM ID] = 24557391

Note: ATTOM Cloud is a dynamic platform for comprehensive property data. It is our goal to ensure that the latest data is made available as quickly as possible for you to use in your projects.