Table Access Protocol (TAP) table joins

import numpy as np
import matplotlib.pyplot as plt
import pandas as pd
from lsst.rsp import get_tap_service
from lsst.utils.plotting import (get_multiband_plot_colors,
                                 get_multiband_plot_symbols)

service = get_tap_service("tap")
assert service is not None

filters = pd.DataFrame.from_dict(get_multiband_plot_colors(), orient='index', columns=['color'])
dum = pd.DataFrame.from_dict(get_multiband_plot_symbols(), orient='index', columns=['symbol'])
filters = pd.merge(filters, dum, left_index=True, right_index=True)
del dum
filters

queries = pd.DataFrame(columns=['select', 'from', 'join', 'on', 'where', 'order by'])
queries.loc['fsodo'] = ["visit, band, psfFlux",
                        "dp1.ForcedSourceOnDiaObject", "", "",
                        "diaObjectId = 614435753623027782", ""]
queries.loc['vis']  = ["visit, expMidptMJD", 
                       "dp1.Visit", "", "", "", ""]
queries.loc['join'] = ["fsodo.band, fsodo.psfFlux, vis.expMidptMJD", 
                       "dp1.ForcedSourceOnDiaObject as fsodo", 
                       "dp1.Visit as vis",
                       "vis.visit = fsodo.visit",
                        "fsodo.diaObjectId = 614435753623027782", ""]
cols = queries.columns
queries

def grabout():
    for idx, dat in queries.iterrows():
        query = ''
        for col in cols:
            if dat[col] != '':
                query += f' {col.upper()} {dat[col]}'
        print(idx); print(query)
        if 'schema' not in dat['from']:
            job = service.submit_job(query)
            job.run()
            job.wait(phases=['COMPLETED', 'ERROR'])
            if job.phase == 'ERROR':
                job.raise_if_error()
            elif job.phase == 'COMPLETED':
                out = job.fetch_result().to_table()
        else:
            out = service.search(query).to_table()
        queries.loc[idx, 'out'] = out
        print(type(out), len(out))
#       display(out)
        print()
grabout()

queries.loc['join', 'out']

fig = plt.figure(figsize=(5, 5))
for band in filters.index:
    idx = np.where(queries.loc['join', 'out']['band'] == band)[0]
    dat = queries.loc['join', 'out'][idx]
    plt.plot(dat['expMidptMJD'],  dat['psfFlux'], filters.loc[band, 'symbol'],
             color=filters.loc[band, 'color'], label=band)
plt.xlabel('MJD')
plt.ylabel('PSF direct image flux (nJy)')
plt.legend(ncols=2)
plt.title('DiaObject 614435753623027782')
plt.show()