david clarck david clarck - 17 days ago 5
MySQL Question

Add Column in Table with conditional in block WHERE

I was doing a querie with

MySQL
to save all objects returned, but I'd like identifie theses objects based in statements of the block
WHERE
, that is, if determined object to satisfy the specific characteristic I'd like create one column and in this cloumn I assignment the value 0 or 1 in the row corresponding the object if it satisfy or not satisfy these characteristic.

Follows my script:

SELECT

s.specObjID, gal.objID, gal.u, gal.g, gal.r, gal.i, s.z,


FROM galaxy as gal, specObj as s


WHERE

s.bestObjID = gal.objID
AND gx.specObjID=s.specObjID
AND s.class='galaxy'
AND s.z between 1 and 1
AND s.ra BETWEEN 15 and 16
AND s.dec BETWEEN 67 and 72
AND s.zWarning = 0


The script above is working perfectly and I can to save all objects which it return.

So, I'd like to know if is there a way add in the querie above, on block
WHERE
, the following statement,

( Flags & (dbo.fPhotoFlags('SATURATED') +
dbo.fPhotoFlags('BRIGHT') +
dbo.fPhotoFlags('EDGE')) ) = 0
and petroRad_r < 18
and ((colc_u - colc_g) - (psfMag_u - psfMag_g)) < -0.4


and determined the objects that satisfy or not these condition with 0 or 1 in a new column created in Table saved.

I read some tutorials about this and saw some attempts with
IF
,
CASE
,
ADD COLUMN
or
WHEN
, but none of these solved.

Thanks in advance

Answer

MySQL has if function, see here

So you can simply use it in your query:

SELECT IF(( Flags & (dbo.fPhotoFlags('SATURATED') +
dbo.fPhotoFlags('BRIGHT') +
dbo.fPhotoFlags('EDGE')) ) = 0
and petroRad_r < 18
and ((colc_u - colc_g) - (psfMag_u - psfMag_g)) < -0.4
, 1 --// VALUE IF TRUE
, 0 --// VALUE IF FALSE
) as conditional_column, ... rest of your query