1

I'm trying to use LIKE inside an IF clause in mySQL and getting an error.

SELECT nc.web_name AS company_name,
    IF((b.booth_number LIKE '%Cafe%', b.booth_number, substring(b.booth_number,4)) AS booth_number, 
    nc.site_url AS website, IF (nc.ismi_status = 1, 'Member','') AS member_status 
    FROM booth_sale bs JOIN {ismi.booth} b ON bs.booth_id = b.booth_id 
    JOIN ismi.new_people np ON bs.contact_id = np.id 
    JOIN ismi.new_company nc ON nc.id = np.company_id 
    WHERE b.show_event_id = 298 AND status IN(44,45) ORDER BY 3

The query works fine if I do this:

IF((b.booth_number = 'Cafe', b.booth_number, substring(b.booth_number,4)) AS booth_number

But I need to check for multiple possibilities - there could be Cafe, Cafe1, Cafe2, etc. and I want to return b.booth_number for any record where that field value includes Cafe.

This is the error I get:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 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 'AS booth_number, nc.site_url AS website, IF (nc.ismi_status = 1, 'Member','') ' at line 2: SELECT nc.web_name AS company_name, IF((b.booth_number LIKE '%Cafe%', b.booth_number, substring(b.booth_number,4)) AS booth_number, nc.site_url AS website, IF (nc.ismi_status = 1, 'Member','') AS member_status FROM {ismi.booth_sale} bs JOIN {ismi.booth} b ON bs.booth_id = b.booth_id JOIN {ismi.new_people} np ON bs.contact_id = np.id JOIN {ismi.new_company} nc ON nc.id = np.company_id WHERE b.show_event_id = 298 AND status IN(44,45) ORDER BY 3

Can anyone tell me what I'm doing wrong, or if there's another way to do this?

Kermit
  • 33,827
  • 13
  • 85
  • 121
EmmyS
  • 11,892
  • 48
  • 101
  • 156
  • Nice catch by newfurniturey. I want to point out it may be a bit risky having `booth_number` alias to `booth_number`. Differentiating may be helpful. – Smandoli Sep 27 '12 at 20:20
  • That's a good point. I inherited this code, and it's working, so I didn't fiddle with it. But I'll make a note in the code to do that. – EmmyS Sep 27 '12 at 20:30

1 Answers1

4

Your IF statement has one-too-many opening parentheses. Drop the first one and you should be good (pending any other SQL errors, of course):

...
IF(b.booth_number LIKE '%Cafe%', b.booth_number, substring(b.booth_number,4)) AS booth_number, 
...
newfurniturey
  • 37,556
  • 9
  • 94
  • 102
  • Thanks! I had originally thought there could only be two possible values so I had an OR inside the IF; I guess I missed one of the opening parens when I was cleaning it up. – EmmyS Sep 27 '12 at 20:20