CODEWITHSUNDEEP
sqloracle-database
depecheSoul
depecheSoul

Reputation: 946

Comparing two rows in a table

as part of homework i need to see if two given lectures in the table have the same teacher.

table consists of

lectureNr | nameOfTheLecture | hoursInWeek | professorID

I did the assingment, it is complicated, but I know only to do it this way:

select distinct ( 
       case when 
       (select professorID from vorlesungen where nameOfTheLecture like 'Logik')=
       (select professorID from vorlesungen where nameOfTheLecture like 'Erkenntnistheorie') 
       then 'yes' else 'no' end) 
from vorlesungen;

can anybody sugest to me a better solutiuon for this.

Thanks

Upvotes: 0

Views: 44

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

You could use ANALYTIC function ROW_NUMBER.

with data as(
select lectureNr,
       nameOfTheLecture,
       hoursInWeek,
       professorID,
       row_number() over(partition by professorID order by professorID, nameOfTheLecture) rn
from vorlesungen
where nameOfTheLecture in ('Logik','Erkenntnistheorie')
)
select * from data
where rn > 1
/

Upvotes: 1

juergen d
juergen d

Reputation: 204746

select count(distinct professorID) as prof_count
from vorlesungen
where nameOfTheLecture in ('Logik','Erkenntnistheorie')

if the count is greater 1 then they are not the same one.

Upvotes: 2

Related Questions