MySQL内连接/左连接

I have the following MySQL statement

SELECT * FROM node
        LEFT JOIN field_data_field_featured ON field_data_field_featured.entity_id = node.nid 
        LEFT JOIN field_data_field_mobile_only ON field_data_field_mobile_only.entity_id = node.nid AND field_data_field_mobile_only.field_mobile_only_value = 1
        INNER JOIN field_data_field_related_show ON field_data_field_related_show.entity_id = node.nid 
        WHERE node.status = '1' AND (node.type IN ('game','video','post')) 
        AND field_related_show_nid = 64
        ORDER BY field_featured_value DESC, node.created DESC

It currently returns all records that fit the conditions stated but I would like to add a conditional statement where if field type is a game I would only like to include the record in the result set if the field field_mobile_only_value is 1.

A sample record of the current SQL looks like this

      nid: 351
                    vid: 351
                   type: game
               language: und
                  title: Avalanche at Plankton's Peak
                    uid: 53
                 status: 1
                created: 1403730218
                changed: 1403730218
                comment: 0
                promote: 1
                 sticky: 0
                   tnid: 0
              translate: 0
            entity_type: node
                 bundle: game
                deleted: 0
              entity_id: 351
            revision_id: 351
               language: und
                  delta: 0
   field_featured_value: 0
            entity_type: NULL
                 bundle: NULL
                deleted: NULL
              entity_id: NULL
            revision_id: NULL
               language: NULL
                  delta: NULL
field_mobile_only_value: NULL
            entity_type: node
                 bundle: game
                deleted: 0
              entity_id: 351
            revision_id: 351
               language: und
                  delta: 0
 field_related_show_nid: 64

I don't think you can add a column to the result based on the value of another column in your select query.

Why don't you verify this in your php code?

If it is not a game, just ignore the field_mobile_only_value column.

You could add something like this to the WHERE clause:

AND ( ( node.type = 'game' 
      AND field_data_field_mobile_only.field_mobile_only_value = 1 
      )
    OR node.type <> 'game'
    )

(We can use the inequality operator here, without a concern for NULL values, because a previous predicate has already guaranteed that the values of node.type will not be NULL.)

If type is 'game', then we only return the row if is equal to 1.

Otherwise, if type is something other than game, we allow the row to be returned.

In the more general case, where we would want to also return rows that had NULL values for type, a slight modification using the null-safe comparator:

AND ( ( node.type = 'game'
      AND field_data_field_mobile_only.field_mobile_only_value = 1
      )
    OR NOT ( node.type <=> 'game' )
    )

would give an equivalent result.