I have two tables in my SQL database:
Table 1: Person:
| Application | NoPerson | Name | Lastname | Salary | DateofBirth |
|---|---|---|---|---|---|
| 01 | 01 | name1 | lastname1 | 2000 | 1990-03-03 |
| 01 | 02 | name2 | lastname2 | NULL | 2010-01-01 |
| 02 | 01 | name1 | lastname1 | 2000 | 1993-03-03 |
| 02 | 02 | name2 | lastname2 | NULL | 2012-01-01 |
Table 2 : EducationInfo:
| Application | NoPerson | schoolgrade | school | goals |
|---|---|---|---|---|
| 01 | 02 | 8. grade | St. Luis | Be Engineer |
| 02 | 02 | 3. grade | norcross | Be Professor |
I need to make a query that returns something like this: If the person is an adult, then show in column occupation his salary. And if it is a child, then show in same column the school grade.
Like this:
CodePudding user response：
Try to use something like this:
select ...., coalesce(p.Salary, e.schoolgrade) as ocupation
from Person p
left join EducationInfo e on e.Aplication=p.Aplication and e.NoPerson=p.NoPerson
We join two tables and then show in ocupation column value Salary or schoolgrade depending on Salary is null or not.
CodePudding user response：
thaks for help, i does this finally
Select *,
CASE WHEN
(P.Salary=0)THEN e.schoolgrade
ELSE CAST(p.Salary AS VARCHAR)
END AS Ocupation
FROM v_APP_Person AS P
LEFT JOIN v_APP_Education AS e ON p.Aplication= e.Aplication and p.NoPerson=e.NoPerson