15,901,035 members
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
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"?

## 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

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

## 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
(...)```

v3
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