Click here to Skip to main content
15,901,035 members
Please Sign up or sign in to vote.
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
Updated 14-Mar-13 10:48am
v2
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"?

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
 
Share this answer
 
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
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
(...)
 
Share this answer
 
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

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900