CODEWITHSUNDEEP
mysqlsql
bjk116
bjk116

Reputation: 529

Geting a CASE statement to work with subquery/join

I am using MySQL 5.6.

I have a query for a table view_listInvoices that has a column state which is an integer from 1-7, but as of now is mostly NULL.

What I'd like is to query for this state, and if it is some values, to display ---, and if it is other values, to JOIN on a description table, descinvoicestate and display the description.

State        Desired Value
NULL         ----
1            descinvoice.description where view_listInvoice.state = descinvoicestate.idx
2            descinvoice.description where view_listInvoice.state = descinvoicestate.idx
3            descinvoice.description where view_listInvoice.state = descinvoicestate.idx
4            ---
5            ---
6            ---
7            descinvoice.description where view_listInvoice.state = descinvoicestate.idx

The descinvoicestate table is one of id's and a description column and thats it.

My current query which is not working is 

SELECT
otherColumns...,
(SELECT CASE WHEN `view_listInvoices`.`state` IS NULL OR `view_listInvoices`.`state` IN (4, 5, 6) THEN "---"
ELSE (SELECT `descinvoicestate`.`description` 
FROM `view_listInvoices`  
JOIN `descinvoicestate`  
ON `descinvoicestate`.`idx` = `view_listInvoices`.`state`) END) as 'Prep'

FROM `view_listInvoices`

I keep getting that I have syntax error but I don't know what is wrong. Help?

Upvotes: 0

Views: 28

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I would use a LEFT JOIN and COALESCE():

SELECT otherColumns...,
       COALESCE(dis.description, '---') as prep
END   prep
FROM `view_listInvoices` li LEFT JOIN
     `descinvoicestate` dis
     ON dis.`idx` = li.`stat;

This is a fairly common type of logic to implement.

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You cannot use SELECT in this manner with CASE .. WHEN. Also, you can utilize Correlated Subqueries here. Try the following instead:

SELECT
/* otherColumns..., */

CASE WHEN v.`state` IS NULL 
           OR v.`state` IN (4, 5, 6) 
     THEN '---' 
     ELSE ( SELECT d.description 
            FROM `descinvoicestate` AS d 
              ON d.`idx` = v.`state`
            LIMIT 1 ) 
     END as 'Prep'
FROM `view_listInvoices` AS v

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You should use a select without nesting 

SELECT
otherColumns...,
CASE WHEN `view_listInvoices`.`state` IS NULL OR `view_listInvoices`.`state` IN (4, 5, 6) 
    THEN "---"
    ELSE `descinvoicestate`.`description`
END   prep
FROM `view_listInvoices`  
JOIN `descinvoicestate`  ON `descinvoicestate`.`idx` = `view_listInvoices`.`stat

or if not all rows nathc between the tables use left join 

SELECT
otherColumns...,
CASE WHEN `view_listInvoices`.`state` IS NULL OR `view_listInvoices`.`state` IN (4, 5, 6) 
    THEN "---"
    ELSE `descinvoicestate`.`description`
END   prep
FROM `view_listInvoices`  
LEFT JOIN `descinvoicestate`  ON `descinvoicestate`.`idx` = `view_listInvoices`.`stat

Upvotes: 1

Related Questions