Joining two sql tables with one to large relationship but maximum second table required
I am trying to concatenate two tables, one of which is a unique feature, the second is the counts taken on multiple dates that are unique features. I want all the records in the first table to be added recently. I was able to get the results I was looking for before adding the form field. Using code
SELECT
Table1.Name, Table1.ID, Table1.Shape,
Max(Table2.DATE) as Date
FROM
Table1
LEFT OUTER JOIN
Table2 ON Table1.ID = table2.ID
GROUP BY
Table1.Name, Table1.ID, Table1.Shape
The form field is a geometry type and I am getting the error
'Geometry type is not comparable. It cannot be used in the Conditional group.
So I need to go differently but don't know how to do it.
Below is a sample of two tables and the desired results.
Table1
Name| ID |Shape
AA1 | 1 | X
BA2 | 2 | Y
CA1 | 3 | Z
CA2 | 4 | Q
Table2
ID | Date
1 | 5/27/2013
1 | 6/27/2014
2 | 5/27/2013
2 | 6/27/2014
3 | 5/27/2013
3 | 6/27/2014
My desired result
Name| ID |Shape |Date
AA1 | 1 | X | 6/27/2014
BA2 | 2 | Y | 6/27/2014
CA1 | 3 | Z | 6/27/2014
CA2 | 4 | Q | Null
source to share
You can aggregate on Table2
in the CTE by searching MAX(DATE)
for each id and then concatenate that result to Table1
:
WITH AggregatedTable2(ID, MaxDate) AS
(
SELECT
ID, MAX(DATE)
FROM
Table2
GROUP BY
ID
)
SELECT
t1.ID, t1.Name, t1.Shape, t2.MaxDate
FROM
Table1 t1
LEFT JOIN
AggregatedTable2 t2 ON t1.ID = t2.ID
source to share