How can I add a third table to a cross-matched table?

Question

I want to combine sdss_specobj tables with gaia and sdss_photoplate using  the cross_match tables. My query is

query = """

SELECT *

FROM sdss_dr17.x1p5__specobj__gaia_dr3__gaia_source as j

JOIN sdss_dr17.specobj as s ON j.id1 = s.specobjid

JOIN gaia_dr3.gaia_source as g ON j.id2 = g.source_id

JOIN sdss_dr17.photoplate as p ON s.bestobjd = p.objid

LIMIT 10"""

It works until I add the photoplate table. I'd be grateful for any suggestions - thanks!  bob

Answer 1

Hi, this looks to be just a simple typo: s.bestobjid (you have s.bestobjd).
Best regards,
Robert

Answer 2

It works! Thank you Robert! I did not see the missing "i", and was convinced it was my novice grammar. Thanks to you, I'll be doing science this week instead of inventing new swear words. I really appreciate the Astro DataLab and its caregivers.

query = """

SELECT s.bestobjid, s.specobjid, s.ra, s.dec, s.glon, s.glat, s.class, s.elodieteff, \

       s.elodiefeh, s.elodielogg, \

       g.source_id, g.designation, g.ra, g.dec, g.l, g.b, g.parallax, g.teff_gspphot, \

       g.mh_gspphot, g.logg_gspphot, g.rv_template_teff, g.rv_template_fe_h,

       g.rv_template_logg, \

       p.objid, p.ra, p.dec, p.l, p.b, p.clean, p.score, p.type, p.type_u, p.type_g, \

       p.type_r, p.type_i, p.type_z, p.psfmag_u, p.psfmag_g, p.psfmag_r, p.psfmag_i, \

       p.psfmagerr_i, p.psfmagerr_z, p.probpsf, p.probpsf_u, p.probpsf_g, p.probpsf_r, \

       p.probpsf_i, p.probpsf_z, p.lnlstar_u, p.lnlstar_g, p.lnlstar_r, p.lnlstar_i, \

       p.lnlstar_z \

FROM sdss_dr17.x1p5__specobj__gaia_dr3__gaia_source as j \

JOIN sdss_dr17.specobj as s ON j.id1 = s.specobjid \

JOIN gaia_dr3.gaia_source as g ON j.id2 = g.source_id \

JOIN sdss_dr17.photoplate as p ON s.bestobjid = p.objid \

WHERE p.clean= 1 AND p.type=6 AND g.teff_gspphot >2000. AND \

               s.elodieteff>2000. \

LIMIT 10"""

result = qc.query(sql=query, fmt='pandas')

result