How to solve the problem with sql query?

Question

PHP

Hello. Quite a complex request. I can't figure out what the problem is.
The request itself:

SELECT
      a.*,
      b.id, b.id_twitter, b.id_facebook, b.name, b.twitter_name, b.email, b.login,
      c.id_sender, c.id_recipient, c.content, c.type, c.read, c.text,
      d.size42, d.size66,
      f.*
      FROM contacts AS a

    		CASE
        WHEN a.type_contact = 0 THEN LEFT JOIN users_app AS b ON b.id = a.id_contact
        WHEN a.type_contact = 1 THEN LEFT JOIN apps AS f ON f.id = a.id_contact
    		END

      LEFT JOIN messages AS c ON c.id = (SELECT MAX(d.id) FROM messages AS d WHERE (d.id_recipient = a.id_user AND d.id_sender = a.id_contact))

      LEFT JOIN icons AS d ON (d.name = c.content OR d.name = c.type)
      WHERE a.id_user = $id_user ORDER BY c.id DESC, a.id DESC

Mistake:

<h1>A Database Error Occurred</h1>
    <p>Error Number: 1064</p><p>You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE
        WHEN a.type_contact = 0 THEN LEFT JOIN users_app AS b ON b.id = a.id_co' at line 9</p><p>SELECT
      a.*,
      b.id, b.id_twitter, b.id_facebook, b.name, b.twitter_name, b.email, b.login,
      c.id_sender, c.id_recipient, c.content, c.type, c.read, c.text,
      d.size42, d.size66,
      f.*
      FROM contacts AS a

    		CASE
        WHEN a.type_contact = 0 THEN LEFT JOIN users_app AS b ON b.id = a.id_contact
        WHEN a.type_contact = 1 THEN LEFT JOIN apps AS f ON f.id = a.id_contact
    		END

      LEFT JOIN messages AS c ON c.id = (SELECT MAX(d.id) FROM messages AS d WHERE (d.id_recipient = a.id_user AND d.id_sender = a.id_contact))

      LEFT JOIN icons AS d ON (d.name = c.content OR d.name = c.type)
      WHERE a.id_user = 255 ORDER BY c.id DESC, a.id DESC</p><p>Filename: models/v2/Contacts_model.php</p><p>Line Number: 28</p>	</div>

To understand the lines:

Answer 1

You've been told what's wrong. Wrong syntax next to CASE. If popular - who told you that you can join different tables depending on some conditions? Well, explain your understanding of the situation. You select "f.* b.id, b.id_twitter, b.id_facebook, b.name, b.twitter_name, b.email, b.login" even though by your own logic the tables with this data may not even be attached. And what will be in the sample then?

Answer 2

The error text unambiguously hints to you that it is impossible to do this: select a table for JOIN in an expression.

Answer 3

CASE doesn't work that way. JOIN both tables

LEFT JOIN users_app AS b ON b.id = a.id_contact AND a.type_contact = 0
LEFT JOIN apps AS f ON f.id = a.id_contact AND a.type_contact = 1