using case statements

Question

0.00/5 (No votes)

See more:

Hi
i have two tables lets call it table a

table A has the following columns

id(int)<br />
symptom(varchar)<br />
weight(int)<br />

Table B has the following

id<br />
TableA_id(int)<br />
Comment<br />
ProductCode<br />

i need to create a view in which i can display all the records with productcode
but the product code has a symptom linked to it from Table A.

So 1 ProductCode could have 5 symptoms

What i want to do is select the productcode, the symptom , the symptom with the highest weight to show High Priority , and all the others to show low priority.

Posted 12-Mar-13 0:38am

isi19

Updated 14-Mar-13 10:48am

Maciej Los

v2

Add a Solution

Comments

Davidduraisamy 12-Mar-13 6:40am

Can you explain with examples

isi19 12-Mar-13 6:56am

Each symptom has a different weight between (0 and 100)

so lets say the symptoms are
Symptom Weight
liquid 50
Cosmetic 20
repair 80

and the ProductCode is C100
select tableb.ProductCode,TableB.Comment,TableA.Symptom(????)as priority from TableA.

so the result of the select statement should show the following
ProductCode comment symptom Priority
C100 N/A liquid low
C100 N/A Cosmetic low
C100 N/A Repair High

gvprabu 12-Mar-13 7:39am

Hi How to choose "Low", "Medium " and "High"?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gvprabu · Answer 1 · 2013-03-12T01:44:00

Solution 2

Hi,

Try This.....

SQL

SELECT B.productcode, B.comment, B.symptom, 
CASE WHEN A.weight BETWEEN 0 AND 30 THEN 'Low' 
     WHEN A.weight BETWEEN 31 AND 60 THEN 'mEDIUM' 
     WHEN A.weight > 60 THEN 'High' 
END 'Priority'
FROM table1 A
INNER JOIN table2 B ON A.id = A.table1_id
WHERE A.productcode IS NOT NULL
ORDER BY B.productcode ASC, A.symptom DESC

Regards,
GVPrabu

Posted 12-Mar-13 1:44am

gvprabu

Comments

isi19 12-Mar-13 7:56am

Hi thanks

but i need the symptom with the highest weight only to show as High and anything with a lower value to show as low

José Amílcar Casimiro · Answer 2 · 2013-03-12T00:46:00

Solution 1

SQL

select b.productcode, a.symptom
from table_a a
inner join table_b b on a.id = b.tablea_id
where not productcode is null
order by b.productcode asc, a.symptom desc

New version:

SQL

select
    b.productcode,
    b.comment,
    a.symptom,
    case
        when a.weight = (select max(weight) from table_a) then 'High'
        else 'Low'
    end as 'Priority'
from
    table_a a
inner join
    table_b b on a.id = b.tablea_id
(...)

Posted 12-Mar-13 0:46am

José Amílcar Casimiro

Updated 12-Mar-13 4:52am

v3

Comments

isi19 12-Mar-13 6:57am

Each symptom has a different weight between (0 and 100)

so lets say the symptoms are
Symptom Weight
liquid 50
Cosmetic 20
repair 80

and the ProductCode is C100
select tableb.ProductCode,TableB.Comment,TableA.Symptom(????)as priority from TableA.

so the result of the select statement should show the following
ProductCode comment symptom Priority
C100 N/A liquid low
C100 N/A Cosmetic low
C100 N/A Repair High