Join two tables without a common column in Oracle-CodePudding

I have two tables -

Employee :

EmployeeNo | EmployeeName | EmployeeDesignation | CompanyID 
12345      | ABC          | Doctor              | 3434
4545       | XYZ          | Engineer            | 3434

Department :

DepartmentNo | EmployeeNo | Location
0808         | 12345      | Loc1
0989         | 12345      | Loc2

I want to print the employee details along with the count of location in the department table. The output should like this -

EmployeeNo| EmployeeName| EmployeeDesignation| Count(Location)
12345     | ABC         | Doctor             | 2
4545      | XYZ         | Engineer           |

When I am using inner join, it is displaying the details of the employeeId 12345 and not of 4545. I know it is so because there is no data for 4545 in the corresponding EmployeeNo column of the Department Table. I know we can use cross join for combining tables without common column but it is giving me a garbage number in the count column.

My query with inner join -


select Employee.EmployeeNo, Employee.EmployeeName, Employee.EmployeeDesignation, count(Department.Location) as count
from Employee inner join Department on
Employee.EmployeeNo = Department.EmployeeNo
where Employee.CompanyID like '3434'
group by Employee.EmployeeNo, Employee.EmployeeName, Employee.EmployeeDesignation

My query with Cross join

select Employee.EmployeeNo, Employee.EmployeeName, Employee.EmployeeDesignation, count(Department.Location) as count
from Employee, Department
where Employee.CompanyID like '3434'
group by Employee.EmployeeNo, Employee.EmployeeName, Employee.EmployeeDesignation

CodePudding user response：

Use a LEFT OUTER JOIN:

SELECT e.EmployeeNo,
       e.EmployeeName,
       e.EmployeeDesignation,
       COUNT(d.Location) as count
FROM   Employee e
       LEFT OUTER JOIN Department d
       ON (e.EmployeeNo = d.EmployeeNo)
WHERE  e.CompanyID like '3434'
GROUP BY
       e.EmployeeNo,
       e.EmployeeName,
       e.EmployeeDesignation

Which, for the sample data:

CREATE TABLE Employee (
  EmployeeNo PRIMARY KEY,
  EmployeeName,
  EmployeeDesignation,
  CompanyID
) AS
SELECT 12345, 'ABC', 'Doctor',   3434 FROM DUAL UNION ALL
SELECT 4545,  'XYZ', 'Engineer', 3434 FROM DUAL;

CREATE TABLE Department (
  DepartmentNo PRIMARY KEY,
  EmployeeNo,
  Location
) AS
SELECT '0808', 12345, 'Loc1' FROM DUAL UNION ALL
SELECT '0989', 12345, 'Loc2' FROM DUAL;

ALTER TABLE Department ADD CONSTRAINT department__employeeno__fk
  FOREIGN KEY (EmployeeNo) REFERENCES Employee(EmployeeNo);

Outputs:

EMPLOYEENO EMPLOYEENAME EMPLOYEEDESIGNATION COUNT

4545 XYZ Engineer 0

12345 ABC Doctor 2

EMPLOYEENO	EMPLOYEENAME	EMPLOYEEDESIGNATION	COUNT
4545	XYZ	Engineer	0
12345	ABC	Doctor	2

Additionally, if EmployeeNo is the primary key then you do not need to include the non-primary key columns in the GROUP BY clause and can aggregate to get those columns:

SELECT e.EmployeeNo,
       MAX(e.EmployeeName) AS EmployeeName,
       MAX(e.EmployeeDesignation) AS EmployeeDesignation,
       COUNT(d.Location) as count
FROM   Employee e
       LEFT OUTER JOIN Department d
       ON (e.EmployeeNo = d.EmployeeNo)
WHERE  e.CompanyID like '3434'
GROUP BY
       e.EmployeeNo

db<>fiddle here