IF statement in mySQL query?

I have this query:

SELECT a.id as alert_id,a.user_id,a.date,a.msg_title,a.message,a.alert_type,a.school_or_contact_id,
u.id as user_id, u.full_name,
c.id as contact_id, concat(c.f_name,' ',c.l_name) as contact_name
FROM alerts a
LEFT OUTER JOIN users u ON a.user_id = u.id
LEFT OUTER JOIN contacts c ON a.school_or_contact_id = c.id
LEFT OUTER JOIN schools s ON a.school_or_contact_id = s.school_id
ORDER BY a.date

This works, but I need it开发者_StackOverflow中文版 to do one more thing, and I can't seem to figure it out. I need to select some data from the "schools" table IF data in alerts.alert_type (alerts table) == "claim".

If "claim" is not found in alerts.alerts_table, then it needs to do nothing different than the query above. alerts.alert_table

This is what I've tried, but it doesn't seem to work:

SELECT a.id as alert_id,a.user_id,a.date,a.msg_title,a.message,a.alert_type,a.school_or_contact_id,
u.id as user_id, u.full_name,
c.id as contact_id, concat(c.f_name,' ',c.l_name) as contact_name,
IF(a.alert_type = 'claim', select s.* from schools where school_id = a.school_or_contact_id)
FROM alerts a
LEFT OUTER JOIN users u ON a.user_id = u.id
LEFT OUTER JOIN contacts c ON a.school_or_contact_id = c.id
LEFT OUTER JOIN schools s ON a.school_or_contact_id = s.school_id
ORDER BY a.date

EDIT For clarification, I'm building a tool that has front page "update" kind of like Facebook. Depending on what the users are doing, the "alerts" will say different things.

The schools table has 3,000 rows and will only apply to the alerts table when the row alerts_type.alerts == "claim". Otherwise, it won't matter what what's in the schools table. If alert_type.alerts != "claim", the "contacts" table will be where the rest of the data comes from.

I wanted to have cleaner data when doing the query (ie -- not "school" table data when alerts_type.alerts != "claim") but I can easily do this in PHP. I just didn't want to pull data that I wouldn't use.

Thank you everyone for all the help and advice!

2nd edit I will change the table schema. Right now, it looks like this:

mysql> desc alerts;
+----------------------+--------------+------+-----+-------------------+----------------+
| Field                | Type         | Null | Key | Default           | Extra          |
+----------------------+--------------+------+-----+-------------------+----------------+
| id                   | int(11)      | NO   | PRI | NULL              | auto_increment | 
| user_id              | int(12)      | YES  |     | NULL              |                | 
| date                 | timestamp    | NO   |     | CURRENT_TIMESTAMP |                | 
| msg_title            | varchar(100) | YES  |     | NULL              |                | 
| message              | longtext     | YES  |     | NULL              |                | 
| alert_type           | varchar(255) | YES  |     | NULL              |                | 
| school_or_contact_id | int(12)      | YES  |     | NULL              |                | 
+----------------------+--------------+------+-----+-------------------+----------------+
7 rows in set (0.00 sec)

I will edit the alerts table to this (below), then JOIN alerts.school_id = schools.school_id. This should fix the problem.

+----------------------+--------------+------+-----+-------------------+----------------+
| Field                | Type         | Null | Key | Default           | Extra          |
+----------------------+--------------+------+-----+-------------------+----------------+
| id                   | int(11)      | NO   | PRI | NULL              | auto_increment | 
| user_id              | int(12)      | YES  |     | NULL              |                | 
| date                 | timestamp    | NO   |     | CURRENT_TIMESTAMP |                | 
| msg_title            | varchar(100) | YES  |     | NULL              |                | 
| message              | longtext     | YES  |     | NULL              |                | 
| alert_type           | varchar(255) | YES  |     | NULL              |                | 
| school_id            | int(12)      | YES  |     | NULL              |                |
| contact_id           | int(12)      | YES  |     | NULL              |                |
+----------------------+--------------+------+-----+-------------------+----------------+

---

You can't really do an optional JOIN like you're trying in the above SQL.

You'll need an IF clause for each column, i.e IF (a.alert_type = 'claim', s.col, NULL)

As you've already joined on the schools table, there shouldn't be any difference in performance, and fetching data all in one query will be better than running multiple queries.

An example:

SELECT a.id as alert_id,a.user_id,a.date,a.msg_title,a.message,a.alert_type,a.school_or_contact_id,
u.id as user_id, u.full_name,
c.id as contact_id, concat(c.f_name,' ',c.l_name) as contact_name,
IF (a.alert_type = 'claim', s.col1, NULL) AS col1,
IF (a.alert_type = 'claim', s.col2, NULL) AS col2
FROM alerts a
LEFT OUTER JOIN users u ON a.user_id = u.id
LEFT OUTER JOIN contacts c ON a.school_or_contact_id = c.id
LEFT OUTER JOIN schools s ON a.school_or_contact_id = s.school_id
ORDER BY a.date

If it happens that you have a lot of fields in the schools table you might as well just fetch s.*, avoid the IF parts, and simply skip over those values in your PHP script.

---

Probably the best way would be to check the alert_type using PHP and run a second query if needed. You could then merge the two results together.

---

You might try this though:

SELECT a.id as alert_id,a.user_id,a.date,a.msg_title,a.message,a.alert_type,a.school_or_contact_id,
u.id as user_id, u.full_name,
c.id as contact_id, concat(c.f_name,' ',c.l_name) as contact_name, s.*
FROM alerts a
LEFT OUTER JOIN users u ON a.user_id = u.id
LEFT OUTER JOIN contacts c ON a.school_or_contact_id = c.id
LEFT OUTER JOIN schools s ON a.school_or_contact_id = s.school_id AND a.alert_type = 'claim'
ORDER BY a.date

---

You can't embed queries into IF() calls. Any reason you can't just do the sub-query unconditionally and then filter the value in your client app? Regardless of this, you cannot have a subquery return multiple fields as you are when the subquery is substituting for a field. So even if the IF() call were possible, the sub-queries have to return a single field/row.

---

Let me introduce you to UNION SELECT.

Note this will be a long query, and depends on the exact structure of schools; the below assumes two columes xs.foo and xs.bar:

SELECT * FROM
  (SELECT a.id as alert_id,a.user_id,a.date,a.msg_title,a.message,
  a.alert_type,a.school_or_contact_id,
  u.id as user_id, u.full_name,
  c.id as contact_id, concat(c.f_name,' ',c.l_name) as contact_name,NULL,NULL
  FROM alerts a
  LEFT OUTER JOIN users u ON a.user_id = u.id
  LEFT OUTER JOIN contacts c ON a.school_or_contact_id = c.id
  WHERE xa.alert_type!='claim'
  UNION SELECT xa.id as alert_id,xa.user_id,xa.date,xa.msg_title,xa.message,
  xa.alert_type,xa.school_or_contact_id,
  xu.id as user_id, xu.full_name,
  xc.id as contact_id, concat(xc.f_name,' ',xc.l_name) as contact_name,xs.foo,xs.bar
  FROM alerts xa
  LEFT OUTER JOIN users xu ON xa.user_id = xu.id
  LEFT OUTER JOIN contacts xc ON xa.school_or_contact_id = xc.id
  LEFT OUTER JOIN schools xs ON xa.school_or_contact_id = xs.school_id
  WHERE xa.alert_type='claim')
ORDER BY date

A caveat: That this is complicated is a good sign your database is poorly designed. If you inherited this...problem, then so be it, but if you're creating new code that works this way, let me strongly recommend that you model your data so a full outer join does the right thing.

---

As others explained, it's notpossible to have variable number of columns in a result set.

The closest you can get to what you want may be this:

SELECT a.id as alert_id
     , a.user_id
     , a.date
     , a.msg_title
     , a.message
     , a.alert_type
     , a.school_or_contact_id
     , u.id as user_id
     , u.full_name
     , c.id as contact_id
     , concat(c.f_name,' ',c.l_name) as contact_name
     , s.* 
FROM alerts a
FROM alerts a
  LEFT OUTER JOIN users u 
    ON  a.user_id = u.id
  LEFT OUTER JOIN contacts c 
    ON  a.school_or_contact_id = c.id
  LEFT OUTER JOIN schools s 
    ON  a.school_or_contact_id = s.school_id
    AND a.alert_type = 'claim' 
ORDER BY a.date