1

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 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 | | +----------------------+--------------+------+-----+-------------------+----------------+
Improve this question
3
  • 1
    Have you had a read of the MySQL manual for the IF statement? It has some examples on there. Commented Sep 19, 2011 at 21:35
  • @Nexerus These docs are for stored procedures not queries, no? Commented Sep 19, 2011 at 21:37
  • can you provide an example of current output and desired output? I don't understand the statement:- "If "claim" is not found in alerts.alerts_table, then it needs to do nothing different than the query above" ... I am confused because the first query will return all the columns from the schools table if the IDs match and null otherwise for each of those columns. Do you only want values returned if alert_type == 'claim' and null otherwire for each row in Alerts? Commented Sep 19, 2011 at 21:41

5 Answers 5

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

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
Improve this answer

Comments

0

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

0

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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.