-- The detectionIndex table contains all detections of a given physical object,
-- which is uniquely identified by the "thingid". In order to extract photometry, 
-- you need a join with the PhotoObjAll table, using the objid.
-- There is also a flag field called isPrimary, which is 1 for the object that
-- is found on a primary polygon/field, all the other detections have 0 in this field.
-- Here is a compound query to find all time series around a certain object with a thingid= 97423000

SELECT
    LTRIM(STR(mjd_r,20,2)) AS MJD,
    dbo.fSDSS(p.objId) AS ID,
    modelMag_g, modelMagErr_g,
    modelMag_r, modelMagErr_r,
    modelMag_i, modelMagErr_i, p.ra, p.dec 
INTO #list
FROM detectionindex d 
    JOIN PhotoObjAll p ON d.objid=p.objid
    JOIN Field f ON p.fieldid=f.fieldid
WHERE d.thingid=97423000
ORDER BY 1
GO
-- 
-- Now find objects near each of the detections 
-- 
SELECT a.*, b.* 
FROM #list a
CROSS APPLY dbo.fGetNearbyObjEq(a.ra, a.dec, 0.1) b
