CODEWITHSUNDEEP
mysql
MrsAdmin
MrsAdmin

Reputation: 548

MySQL case statement error, data not showing for part of query

MySQL Case statement error:

When I use the following, as separate cases, the query works, however when combined they do not.

I am using MySQL Workbench

Error:
Only the Manufacturer Information returns, not the ProdID based query.
The ProdID is correct, I use it to pull the ID prior to this and it returns the correct ID#.

I require them in 1 column to run calculations in the next step.

Not working: 

CASE 
    When(T4.manufacturers_id = 1) then ''
    When(T4.manufacturers_id = 2) then '.10'
    When(T4.manufacturers_id = 3) then '.10'
    When(T4.manufacturers_id = 4) then '0'
    When(T3.products_id = 11) then '.10'
    When(T3.products_id = 34) then '.10'
    When(T3.products_id = 35) then '.10'
    When(T3.products_id = 36) then '.10'
    When(T3.products_id = 37) then '.10'
    When(T3.products_id = 38) then '.10'
    When(T3.products_id = 39) then '.10'
end As Comms,

working: 

CASE 
    When(T4.manufacturers_id = 1) then ''
    When(T4.manufacturers_id = 2) then '.10'
    When(T4.manufacturers_id = 3) then '.10'
    When(T4.manufacturers_id = 4) then '0'
end As MIDComms,

case
    When(T3.products_id = 11) then '.10'
    When(T3.products_id = 34) then '.10'
    When(T3.products_id = 35) then '.10'
    When(T3.products_id = 36) then '.10'
    When(T3.products_id = 37) then '.10'
    When(T3.products_id = 38) then '.10'
    When(T3.products_id = 39) then '.10'
end As PIDComms,

Ideally I'd like it to be: 

    CASE 
    When(T4.manufacturers_id = 1) then ''
    When(T4.manufacturers_id = 2) then '.10'
    When(T4.manufacturers_id = 3) then '.10'
    When(T4.manufacturers_id = 4) then '0'
    When(T3.products_id = 11) then '.10'
    When(T3.products_id) between 34 and 39 then '.10'
end As Comms,

Thanks in advance

Upvotes: 1

Views: 31

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

A case statement only returns one value -- the first one encountered. Perhaps you want some sort of concatenation:

CONCAT_WS(':',
          (CASE When(T4.manufacturers_id = 1) then ''
                When(T4.manufacturers_id = 2) then '.10'
                When(T4.manufacturers_id = 3) then '.10'
                When(T4.manufacturers_id = 4) then '0'
           END),
          (CASE When(T3.products_id = 11) then '.10'
                When(T3.products_id = 34) then '.10'
                When(T3.products_id = 35) then '.10'
                When(T3.products_id = 36) then '.10'
                When(T3.products_id = 37) then '.10'
                When(T3.products_id = 38) then '.10'
                When(T3.products_id = 39) then '.10'
           end) ) As Comms,

Upvotes: 2

Related Questions