duplicate output results in oracle sql

you can use SELECT DISTINCT to remove duplicates.

select distinct employees.department_id, departments.department_name, first_name || ' ' || last_name as full_name, job_title
from departments, employees, jobs
where employees.department_id = departments.department_id
and job_title like '%Sales%'
order by job_title, full_name;

Alternatively you can also use a GROUP BY on each column

select employees.department_id, departments.department_name, first_name || ' ' || last_name as full_name, job_title
from departments, employees, jobs
where employees.department_id = departments.department_id
and job_title like '%Sales%'
group by employees.department_id, departments.department_name, first_name || ' ' || last_name as full_name, job_title
order by job_title, full_name;

It also looks like you're missing a join on jobs? That may be the reason your query is returning duplicate results.

I'd also recommend using explicit join syntax when you can.

FROM departments
 INNER JOIN employees ON employees.department_id = departments.department_id

You can use a DISTINCT as Matt Busche indicates.

In many, many cases, however, people incorrectly add a DISTINCT to cover up the fact that they are missing a join condition. In your case, you are joining three tables together-- departments, employees, and jobs-- but you only have one join condition. You are, therefore, doing a Cartesian join to the jobs table which is almost certainly not what you want. How does the jobs table relate to the other two tables? If we assume that there is a job_id in both the jobs and the employees table, you would want something like

select employees.department_id, 
       departments.department_name, 
       first_name || ' ' || last_name as full_name, 
       job_title
  from departments, employees, jobs
 where employees.department_id = departments.department_id
   and jobs.job_id             = employees.job_id
   and job_title            like '%Sales%'
 order by job_title, full_name;

I guess Union all can produce a result like that. Basically, you want to duplicate each distinct row of your dataset from what I have understood.

For example, if the query is like this: Find employees in the HR department and output the result with one duplicate. Output the first name and the department of employees.

Table name: Worker Columns: worker_idint first_name varchar last_name varchar salary int joining_date datetime department varchar

select first_name , department from worker where department='HR' union all select first_name , department from worker where department='HR';

This will give each row twice like you have it in your output.

So, in you case, you can fetch distinct records in a temp table, and then make self-join on same table to shape the outcome as you want. It will be too much of a task, but I could think of the solution in this direction.