CODEWITHSUNDEEP
sqloracle-database
user3017335
user3017335

Reputation: 285

How do I compose this sql query in Oracle?

I have got an sql query that pulls out all sorts of information. Part of it is the following

  select gsm.mobile_no, emp.employee_id, d.department_id
  from data gsm, employees emp, department d
  where gsm.code = e.code
  and d.id = e.id

Now there's a column called roaming in another table called "call" . Here's the problem. There's information from the call table for only some of the mobile numbers so when I join gsm.code = call.id like below

  select gsm.mobile_no, emp.employee_id, d.department_id, roaming.name 
  from data gsm, employees emp, department d, call roaming
  where gsm.code = e.code
  and d.id = e.i
  and roaming.id = gsm.code

Then I lose information about employees and departments since only the records that satisfy the condition roaming.id = gsm.code are retrieved so I lose info about departments, employees and all other mobile numbers. I want to retrieve all records from all tables including roaming.id for the mobile numbers where applicable and if there's no data available for some of the mobile numbers then display null but I want all of the records displayed. How could I do that?

Upvotes: 0

Views: 69

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Your time has come to move to the world of modern join syntax. Put your join conditions in the on clause and remember the simple rule: Never use a comma in the from clause.

What you need is a left outer join. You can't really do that in the where clause. Well, you can in Oracle, but it is not pretty and not as good as a real left outer join.

select gsm.mobile_no, emp.employee_id, d.department_id, roaming.name 
from employes left outer join
     data gsm
     on gsm.code = e.code left join
     department d
     on d.id = e.i left outer join
     call roaming
     on  roaming.id = gsm.code;

Although you can mix inner and outer joins, you want to keep all employees. So start with that table and make all the joins left outer join.

Upvotes: 3

Related Questions