Question about triple ADQL join

Support Rubin Science Platform
#1

Hi,
I was trying to join three tables on RSP using something like:

"SELECT …
"FROM dp01_dc2_catalogs.object as obj "
"INNER JOIN dp01_dc2_catalogs.reference as ref ON (obj.objectID=ref.objectID) "
"INNER JOIN dp01_dc2_catalogs.truth_match as truth ON (obj.objectID=truth.match_objectId) "
"WHERE …

obtaining this DALQueryError: Query processing error: QI=?: Failed to instantiate query: AnalysisError: Query involves partitioned table joins that Qserv does not know how to evaluate using only partition-local data

I would like to know how to run this kind of triple join. Thank you in advance, m.

#2

This query has syntax errors by referring to the non-existing columns objectID on the tables object and reference. Note that database and table names are case-sensitive in Qserv.

#3

After correcting your query, I could get the desired result:

SELECT COUNT(*) FROM dp01_dc2_catalogs.object as obj
  INNER JOIN dp01_dc2_catalogs.reference as ref ON obj.objectId=ref.objectId
  INNER JOIN dp01_dc2_catalogs.truth_match as truth ON (obj.objectId=truth.match_objectId)
  WHERE objectId=11989512875933697;
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 Like
#4

Thank you, Igor!