Join desi_dr1.x1p5__zpix__gaia_dr3__gaia_source to other tables

Question

I am having trouble joining desi_dr1.x1p5__zpix__gaia_dr3__gaia_source to any of the other DESI DR1 tables. For example:

SELECT TOP 10 *

FROM desi_dr1.x1p5__zpix__gaia_dr3__gaia_source

JOIN  desi_dr1.target ON  desi_dr1.target.id = desi_dr1.x1p5__zpix__gaia_dr3__gaia_source.id1

returns 0 results. Any advice on what the correct join columns are?

Answer 1

Sorry, I think I answered my own question. It seems like you have to join from desi_dr1.x1p5__zpix__gaia_dr3__gaia_source to desi_dr1.zpix on id, and then desi_dr1.zpix to desi_dr1.target on targetid. This seems confusing to me as the id column description is the same in both desi_dr1.zpix and desi_dr1.target, so it is unclear to me why that additional join is needed?

Comments

Hi,
The reason the additional join is needed is because the id column was created with a different set of column in the zpix table versus in the target table. The id column in desi_dr1.zpix was made from targetid, survey, program whereas the id column in desi_dr1.target was made from targetid, survey, tileid.