SELECT o.*,
Isnull(nbor.nearest,999) AS nearest
FROM (
-- This selects the white dwarf candidates, meeting the following criteria
-- 1) Stars with dereddened g magnitudes between 15 and 20
-- 2) Proper motion > 2 arcsec/century
-- 3) Meet either a reduced proper motion cut, or have dereddened g-i < 0
-- A left outer join is also performed to fetch the spectroscopic information
-- for those stars with spectra.
SELECT p.objid,
p.psfmag_g - p.extinction_g + 5 * LOG(u.propermotion / 100.) + 5 AS rpm,
p.psfmag_g - p.extinction_g - (p.psfmag_i - p.extinction_i) AS gi,
Isnull(s.plate,0) AS plate,
Isnull(s.mjd,0) AS mjd,
Isnull(s.fiberid,0) AS fiberid
FROM phototag p
JOIN usno u
ON p.objid = u.objid
LEFT OUTER JOIN specobj s
ON p.objid = s.bestobjid
WHERE p.TYPE = dbo.Fphototype('Star')
AND (p.flags & dbo.Fphotoflags('EDGE')) = 0
AND (p.psfmag_g - p.extinction_g) BETWEEN 15
AND 20
AND u.propermotion > 2.
AND (p.psfmag_g - p.extinction_g + 5 * LOG(u.propermotion / 100.) + 5 > 16.136 + 2.727 * (p.psfmag_g - p.extinction_g - (p.psfmag_i - p.extinction_i))
OR p.psfmag_g - p.extinction_g - (p.psfmag_i - p.extinction_i) < 0.)) AS o
LEFT OUTER JOIN (
SELECT n.objid,
MIN(n.distance) AS nearest
FROM neighbors n
JOIN phototag x
ON n.neighborobjid = x.objid
WHERE n.TYPE = dbo.Fphototype('Star')
AND n.mode = dbo.Fphotomode('Primary')
AND n.neighbormode = dbo.Fphotomode('Primary')
AND (x.TYPE = dbo.Fphototype('Star')
OR x.TYPE = dbo.Fphototype('Galaxy'))
AND x.modelmag_g BETWEEN 10
AND 21
GROUP BY n.objid) AS nbor
ON o.objid = nbor.objid
SQL before beautify
SELECT
o.*, ISNULL(nbor.nearest,999) as nearest
FROM
(
-- This selects the white dwarf candidates, meeting the following criteria
-- 1) Stars with dereddened g magnitudes between 15 and 20
-- 2) Proper motion > 2 arcsec/century
-- 3) Meet either a reduced proper motion cut, or have dereddened g-i < 0
-- A left outer join is also performed to fetch the spectroscopic information
-- for those stars with spectra.
SELECT p.objID,
p.psfMag_g - p.extinction_g + 5 * log(u.propermotion / 100.) + 5 AS rpm,
p.psfMag_g - p.extinction_g - (p.psfMag_i - p.extinction_i) AS gi,
ISNULL(s.plate,0) as plate, ISNULL(s.mjd,0) as mjd,
ISNULL(s.fiberID,0) as fiberID
FROM PhotoTag p JOIN USNO u ON p.objID = u.objID
LEFT OUTER JOIN SpecObj s ON p.objID = s.bestObjID
WHERE p.type = dbo.fPhotoType('Star') AND (p.flags & dbo.fPhotoFlags('EDGE')) = 0
AND (p.psfMag_g - p.extinction_g) BETWEEN 15 AND 20 AND u.propermotion > 2.
AND (p.psfMag_g - p.extinction_g + 5 * log(u.propermotion / 100.) + 5 >
16.136 + 2.727 * (p.psfMag_g - p.extinction_g -
(p.psfMag_i - p.extinction_i)) OR
p.psfMag_g - p.extinction_g - (p.psfMag_i - p.extinction_i) < 0.)
) AS o
LEFT OUTER JOIN
(
SELECT n.objID, MIN(n.distance) AS nearest
FROM Neighbors n JOIN PhotoTag x ON n.neighborObjID = x.objID
WHERE n.type = dbo.fPhotoType('Star') AND
n.mode = dbo.fPhotoMode('Primary') AND
n.neighborMode = dbo.fPhotoMode('Primary') AND
(x.type = dbo.fPhotoType('Star') OR x.type = dbo.fPhotoType('Galaxy'))
AND x.modelMag_g BETWEEN 10 AND 21
GROUP BY n.objID
) AS nbor ON o.objID = nbor.objID