-- Ellipticals with odd lines. 
-- Find all elliptical galaxies with spectra that have an anomalous
-- emission line.  This query introduces the SQL syntax DISTINCT, which will
-- return only one instance of the requested parameter (ObjID, in this case),
-- because our query may return the same object more than once.  This is also
-- the first nested query, where we use one SELECT (the inner one) to get a
-- group of objects we are not interested in. The outer SELECT includes the
-- new syntax 'not in', which is used to perform the exclusion.

SELECT DISTINCT G.ObjID		-- return qualifying galaxies
into mydb.sampletest
FROM
  Galaxy     as G,		-- G is the galaxy
  SpecObjAll    as S,		-- S is the spectra of galaxy G
  SpecLineAll   as L,		-- L is a line of S
	-- we must use SpecLineAll because SpecLine only has ID's lines
  XCRedshift as XC		-- the template cross-correlation redshift
WHERE G.ObjID = S.bestObjID	-- connect galaxy to the spectrum
  and S.SpecObjID = L.SpecObjID   -- The line L must be in the spectrum S
  and S.SpecObjID = XC.SpecObjID  -- and there is a crosscorrelation redshift
  and XC.tempNo = 8               -- Template('Elliptical') -- best Cross correlation template used is "elliptical"
  and L.LineID = 0
  and L.ew > 10                   -- and the line is prominent by some definition, in this case, the equivalent width is over 10 Angstroms
  and S.SpecObjID not in ( 
        -- insist that there are no other lines
    SELECT S.SpecObjID            -- This is the chosen line.
    FROM    SpecLine as L1        -- L1 is another line...
	-- by using SpecLine, we are automatically requiring that the line
	-- be identified
    WHERE S.SpecObjID = L1.SpecObjID   -- ...for this object...
      and abs(L.wave - L1.wave) <.01 
        -- ...at nearly the same wavelength...
  )
