Querying Multiple tables?

Question

I am new to queries and learning it. I have two tables, Emp and EmpDetail. I use a query to select the Emp like

Select Id,Name,Date where Date >= trunc(sysdate)-10

And the EmpDetails have more fields but I just have to select few filelds like

Select act,rol,comp,rescd from EmpDetail

Now to select records from both the table like having some conditions

Select em.Id
     , em.Name
     , em.Date
     , ed.act
     , ed.rol
     , ed.comp
     , ed.rescd 
  from Emp em
     , EmpDetail ed 
 where em.Id = ed.rescd 
   and ed.rol = 'req' 
   and em.date != Null

So using Inner Join

Select Emp.Id,Emp.Name,Emp.Date,EmpDetail.act,EmpDetail.rol,EmpDetail.comp,EmpDetail.rescd from Emp INNER JOIN EmpDetail where Emp.Id=EmpDetail.rescd and EmpDetail.rol='req' and Emp.date!=Null

Am I doing it correct

You could use the `join `. `from Emp em join EmpDetails ed on em.Id=ed.rescd and ed.rol='req' and em.date!=Null` It will genrate the same plan for both of them, But the joins makes it clear and understandable to read . — Mahesh, Mar 17 '15 at 13:41
SImple `Inner JOIN` would suffice. [Read from here](http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join) — Mahesh, Mar 17 '15 at 13:44
@Strawberry I dont get you. != (Not Equal To) doesnot work here? I have to select dates which are not NULL — xyz, Mar 17 '15 at 14:07
This statement -> `and em.date != Null` will always evaluate to NULL. I.e, it will never be true. — Strawberry, Mar 17 '15 at 14:08
the table has Date record with mostly NULL in it only some has dates on it. I have select the records which doesnot have NULL values how can I do that? — xyz, Mar 17 '15 at 14:11

Stanislovas Kalašnikovas · Accepted Answer · 2015-03-17T14:19:15.147

1

You can use JOIN.

SELECT e.Id, e.Name, e.Date, ed.act, ed.rol, ed.comp, ed.rescd
FROM Emp AS e 
INNER JOIN EmpDetails AS ed 
ON e.Id LIKE CONCAT(%, ed.rescd)
WHERE e.Date >= trunc(sysdate)-10

edited Mar 17 '15 at 14:19

answered Mar 17 '15 at 13:52

Stanislovas Kalašnikovas

9,203
4
32
59

How can I truncate if there are leading zeros in the ed.rescd – xyz Mar 17 '15 at 14:05
No I am wondering if having e.Id as 1234 and ed.rescd as 00000001234 this query will work right – xyz Mar 17 '15 at 14:09
Check updated answer, you can use `LIKE` and `WILDCARD %`. But this will work too if you have for example `010001234`, in this way no care what will be before `ed.rescd` – Stanislovas Kalašnikovas Mar 17 '15 at 14:20
1

Or if you know that you have exactly 7 x 0 before number you can use `CONCAT(0000000, ed.rescd)` and in this way `=` instead of `LIKE` – Stanislovas Kalašnikovas Mar 17 '15 at 14:22
@@Stanislovas Kalašnikovas . Is there any way to truncating the leading 0000000 in the ed.rescd while comparing instead of concatenating. Just curious – xyz Mar 17 '15 at 14:35

Querying Multiple tables?

1 Answers1