how to call a name with duplicate condition Oracle sql?

Navigation
1

I am a bit confused when trying to construct a query that brings the Names (from the ENAME column) of all the employees (from the employees table) that were not registered in the JAVA course (from the table registrations). the column attendee is the employee's foreign key (course code JAV)

Try the following query, which brings me the employee records that are in the other courses, omitting jav. 

SELECT A.ENAME, B.COURSE
FROM EMPLOYEES A
INNER JOIN REGISTRATIONS B ON A.EMPNO=B.ATTENDEE
WHERE A.EMPNO = B.ATTENDE AND COURSE <> 'JAV'

However, I do not want you to bring me the name of someone who has ever registered in the jav course. ex: 

cod|ename|course
  1|Juan |sql
  2|Pedro|sql
  1|Juan |jav

Now, what I'm looking for is something similar to: 

cod|ename|course
  2|Pedro|sql
    
asked by Deivid Stiven Medina Hernandez 29.06.2018 в 00:18
source

2 answers

1

If you only want the data of employees who have never registered in the java course, the clearest way to do it is: 

SELECT  A.ENAME
        FROM EMPLOYEES A
        WHERE A.EMPNO NOT IN (SELECT ATTENDE 
                                     FROM REGISTRATIONS 
                                     WHERE COURSE = 'JAV')

That is to say: All the employees that were not in REGISTRATIONS with the condition of COURSE = 'JAV'

    
answered by 29.06.2018 / 02:15
source
0

I think this should work for you: 

SELECT A.ENAME, B.COURSE
  FROM REGISTRATIONS B
  LEFT JOIN EMPLOYEES A ON A.EMPNO = B.ATTENDEE
  WHERE B.COURSE <> 'JAV'

How it works: 

SELECT *
  FROM REGISTRATIONS B

The above brings you all the records. Then we filter them to only have those that are not JAV : 

SELECT *
  FROM REGISTRATIONS B
  WHERE B.COURSE <> 'JAV'

Having already filtered the records, we want to know who is assigned: 

SELECT *
  FROM REGISTRATIONS B
  LEFT JOIN EMPLOYEES A ON A.EMPNO = B.ATTENDEE
  WHERE B.COURSE <> 'JAV'

And finally, the columns that are relevant for your query are selected: 

SELECT A.ENAME, B.COURSE
  FROM REGISTRATIONS B
  LEFT JOIN EMPLOYEES A ON A.EMPNO = B.ATTENDEE
  WHERE B.COURSE <> 'JAV'

Now, what is a LEFT JOIN ? It is an operation that is responsible for finding the intersection between both tables, and adhere to the result of the first table ( REGISTRATIONS in our case).

You can read a bit more about it here .

    
answered by 29.06.2018 в 02:23
Error UPDATING Duplicate entry '1' for key 'PRIMARY' Change of order in nav because of the css