How to join two tables using oracle sql query?

Question

i have 2 tables: jobs and cods

     jobs                                          cods

idx   descr                                    cod       descr      idx

1    teacher                                   codx      codingx     1
2    programmer                                cody      codingy     4 
3    sailor                                    codz      codingz     2
4    medic                                     codw      codingw     3
5    student                                   cods      codings     1  
                                               codw      codingw     1

i want the select, return the rows with codx and codw where the idx column is the join

id    descr            codx      desc_codx     codw    descr_codw    
1     teacher          codx      codingx       codw     codingw
2     programmer       null       null         null     null     
3     sailor           null       null         codw     codingw
4     medic            null       null         null     null  
5     student          null       null         null     null

Is it possible with only 1 select?

select x.id, x.descr,a.cod as codx a.desc as desc_codx,b.cod as codw, b.desc as desc_codw 
from jobs x, cods a, cods b 
where x.id= a.id(+) 
  and x.id = b.id(+) 
  and a.cod= 'codx' 
  and b.cod= 'codw'

What is difficult about this? What have you tried?

Gordon Linoff
– Gordon Linoff

2015-11-26 22:45:08 +00:00
Commented Nov 26, 2015 at 22:45 — Gordon Linoff
– Gordon Linoff, Commented Nov 26, 2015 at 22:45

Kamil Gosciminski · Accepted Answer · 2015-11-26 22:51:23Z

2

Yes, it is possible. You need two joins to do that so that you can make separate columns for codx and codw.

SELECT
  j.idx, j.descr,
  c.cod AS codx, c.descr AS desc_codx,
  c2.cod AS codw, c2.descr AS desc_codw
FROM
  jobs j
  LEFT JOIN cods c ON j.idx = c.idx AND c.cod = 'codx'
  LEFT JOIN cods c2 ON j.idx = c2.idx AND c2.cod = 'codw'

You could also do that using only 1 JOIN and working with CASE statements. This may seem like more to write, but performance-wise it is far better, as we scan table cods only once and evaluating CASEs pays off much more.

SELECT
  j.idx, j.descr,
  CASE WHEN c.cod = 'codx' THEN c.cod END AS codx,
  CASE WHEN c.cod = 'codx' THEN c.descr END AS desc_codx
  CASE WHEN c.cod = 'codw' THEN c.cod END AS codw,
  CASE WHEN c.cod = 'codw' THEN c.descr END AS desc_codw
FROM
  jobs j
  LEFT JOIN cods c ON j.idx = c.idx AND c.cod IN ('codx', 'codw')

edited Nov 26, 2015 at 22:51

answered Nov 26, 2015 at 22:46

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kamil Gosciminski Over a year ago

What exactly do you mean by (+) syntax? Doesn't my answer give you expected results?

Kamil Gosciminski Over a year ago

I can see your comment. Why would you use (+) instead of x when calling a column named idx? Also, putting conditions between two tables into WHERE implies an INNER JOIN. This is not what you want. Please, try the code from this answer.

programAll Over a year ago

as you can see my comment to gordon also i made a select with left join using the plus (+) operator you known? i think its' the same...your select go to try Tomorrow if work it's half past midnight.(in my state). thanks see you asap

Kamil Gosciminski Over a year ago

I'm not familiar with a plus operator. Also, your comment doesn't contain idx column names, but id. Could you please provide me with a reference to (+) operator you've mentioned?

Muhammad Amir Nadeem · Accepted Answer · 2015-11-27 11:04:23Z

0

select jobs.* , cods.* from jobs,cods where jobs.idx=cods.idx;

edited Nov 27, 2015 at 11:04

answered Nov 26, 2015 at 22:47

Muhammad Amir Nadeem

95 bronze badges

Collectives™ on Stack Overflow

How to join two tables using oracle sql query?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related