CODEWITHSUNDEEP
mysqlsqlsql-server
user934902
user934902

Reputation: 1204

Adding a case statment to SQL join statement

Im trying to format all my DATA in my sql before display information

Both my joins work fine, I have a piece of data (status) that has associated titles to go with the value but the CASE statment isn't working. I currently have a PHP swtich statement that does this data conversion but I'd like to do it in SQL if possible

SELECT
 l.leadid, l.companyname, l.status, l.dateadded, addedby.name AS addedby, assignedto.name AS assignedto, l.address, l.city, l.prov, l.country, l.postalcode,
 l.phone, l.fax, l.facebook, l.twitter, l.youtube, l.instagram, l.website, l.logo
FROM leads l
  INNER JOIN staff addedby ON l.addedby = addedby.staffid
  INNER JOIN staff assignedto ON l.assignedto = assignedto.staffid
    CASE l.status
     WHEN l.status = 7 THEN l.status = 'Converted'
     WHEN l.status = 6 THEN l.status = 'Hot'
     WHEN l.status = 5 THEN l.status = 'Warm'
     WHEN l.status = 4 THEN l.status = 'Open'
     WHEN l.status = 3 THEN l.status = 'NEW'
     WHEN l.status = 2 THEN l.status = 'Cold'
       ELSE l.status = 'Dead'
     END    
     WHERE l.leadid = $id

Upvotes: 1

Views: 101

Answers (3)

peterm
peterm

Reputation: 92795

  1. You want your CASE in SELECT clause
  2. Your CASE syntax is invalid
  3. As an alternative you can use a MySQL (since your question tagged with mysql) specific less verbose function ELT()

That being said you can do either

SELECT
       l.leadid, l.companyname, 
       CASE l.status
         WHEN 7 THEN 'Converted'
         WHEN 6 THEN 'Hot'
         WHEN 5 THEN 'Warm'
         WHEN 4 THEN 'Open'
         WHEN 3 THEN 'NEW'
         WHEN 2 THEN 'Cold'
         ELSE 'Dead'
       END status, 
       l.dateadded, addedby.name AS addedby, 
       assignedto.name AS assignedto, l.address, 
       l.city, l.prov, l.country, l.postalcode,
       l.phone, l.fax, l.facebook, l.twitter, 
       l.youtube, l.instagram, l.website, l.logo
  FROM leads l JOIN staff addedby 
    ON l.addedby = addedby.staffid JOIN staff assignedto 
    ON l.assignedto = assignedto.staffid   
WHERE l.leadid = $id

or

SELECT
       l.leadid, l.companyname, 
       IFNULL(ELT(l.status - 1, 'Cold', 'New', 'Open', 'Warm', 'Hot', 'Converted'), 'Dead') status,
       l.dateadded, addedby.name AS addedby, 
       assignedto.name AS assignedto, l.address, 
       l.city, l.prov, l.country, l.postalcode,
       l.phone, l.fax, l.facebook, l.twitter, 
       l.youtube, l.instagram, l.website, l.logo
  FROM leads l JOIN staff addedby 
    ON l.addedby = addedby.staffid JOIN staff assignedto 
    ON l.assignedto = assignedto.staffid   
WHERE l.leadid = $id

Here is SQLFiddle demo (for both cases)

Upvotes: 3

Dmitriy Khaykin
Dmitriy Khaykin

Reputation: 5258

The case should be in the SELECT list, not after the INNER JOIN. Also second issue is the use of l.status = after each THEN statement. MySql syntax only requires the final value after the THEN statement, not a value assignment.

Also note that you don't need the l.status = part in your when statements, because the case value is listed next to the CASE statement. If you were building an ad hoc case statement, then it would be needed. So, you can just write it as follows:

I would just write it as follows:

SELECT
    l.leadid, 
    l.companyname, 
    CASE l.status
        WHEN 7 THEN 'Converted'
        WHEN 6 THEN 'Hot'
        WHEN 5 THEN 'Warm'
        WHEN 4 THEN 'Open'
        WHEN 3 THEN 'NEW'
        WHEN 2 THEN 'Cold'
        ELSE 'Dead'
    END As Status, 
    l.dateadded, 
    < rest of your fields >
FROM leads l
    INNER JOIN staff addedby ON l.addedby = addedby.staffid
    INNER JOIN staff assignedto ON l.assignedto = assignedto.staffid
WHERE l.leadid = $id

Upvotes: 2

Gopal Joshi
Gopal Joshi

Reputation: 2358

Try It

SELECT
 l.leadid, l.companyname, l.status, l.dateadded, addedby.name AS addedby, assignedto.name AS assignedto, l.address, l.city, l.prov, l.country, l.postalcode,
 l.phone, l.fax, l.facebook, l.twitter, l.youtube, l.instagram, l.website, l.logo, CASE l.status
     WHEN l.status = 7 THEN l.status = 'Converted'
     WHEN l.status = 6 THEN l.status = 'Hot'
     WHEN l.status = 5 THEN l.status = 'Warm'
     WHEN l.status = 4 THEN l.status = 'Open'
     WHEN l.status = 3 THEN l.status = 'NEW'
     WHEN l.status = 2 THEN l.status = 'Cold'
       ELSE l.status = 'Dead'
     END
FROM leads l
  INNER JOIN staff addedby ON l.addedby = addedby.staffid
  INNER JOIN staff assignedto ON l.assignedto = assignedto.staffid    
WHERE l.leadid = $id

Upvotes: -2

Related Questions