Home » SQL & PL/SQL » SQL & PL/SQL » Self join with inner join followed by a full outer join
Self join with inner join followed by a full outer join [message #303300] Thu, 28 February 2008 16:04 Go to next message
mclovin
Messages: 2
Registered: February 2008
Junior Member
Hello everyone. Thanks for reading this. I have asked several people what Oracle version they are using, but no one knows for sure. I'm sorry I couldn't be more specific, all they can tell me is that it is either 9i or 10g.

I have one table that stores test scores for SAT's, ACT's and other college entrance exams. Each score is recorded in the table with a persons pidm and score, with a code that designates what test it is. There are many other columns, but those are the only ones I need for this.

What I'm trying to do is create a view that has one row for each individual that lists their SAT verbal, SAT math, and ACT scores. I realized that I could use a self join for this, so that is what I'm trying at the moment. I don't want to digress into other details too much, but there is only one record per person for each type of score in this table.

For illustration:
CREATE TABLE myexams(pidm NUMBER(3,0) NOT NULL
                    ,code VARCHAR2(4) NOT NULL
                    ,score VARCHAR2(5)
                    ,CONSTRAINT myexams PRIMARY KEY(pidm, code))

INSERT INTO myexams VALUES(288, 'VERB', '780');
INSERT INTO myexams VALUES(288, 'MATH', '600');
INSERT INTO myexams VALUES(288, 'ACT', '30');
INSERT INTO myexams VALUES(622, 'ACT', '25');
INSERT INTO myexams VALUES(711, 'VERB', '550');
INSERT INTO myexams VALUES(711, 'MATH', '490');
INSERT INTO myexams VALUES(399, 'VERB', '310');

Given the above, what I want to see is a result like this:
pidm  verbal  math  act
----  ------  ----  ---
288   780     600   30
622                 25
711   550     490

... but the order is not important. Note that pidm=399 doesn't show up because they have no corresponding math score. So what I'm trying to get is people that have one of the following conditions:

  • ACT
  • SAT Verbal and Math
  • ACT and SAT Verbal and Math

If one of those is true, then they should show up in the result, but just once.

So I figured that I would use a self join with the first set being all records with a 'VERB' code inner joined with a second set of records with a 'MATH' code on their pidm's. Following that, would be a full outer join with records with a 'ACT' code on their pidm's. This, unfortunately, is harder than it sounds, at least for me.

I have tried a myriad of ways to do this:
SELECT pidm exam_id,
  verbal.score verbal,
  math.score math,
  act.score act
FROM myexams verbal
INNER JOIN myexams math USING(pidm) FULL JOIN myexams act USING(pidm)
WHERE verbal.code = 'VERB'
 AND math.code = 'MATH'
 AND act.code = 'ACT'

which only gives:
288	780	600	30

... and:
SELECT vbl.score verbal,
  mat.score math,
  act.score act
FROM myexams vbl
INNER JOIN myexams mat ON vbl.pidm = mat.pidm
 AND vbl.code = 'VERB'
 AND mat.code = 'MATH' FULL JOIN myexams act ON vbl.pidm = act.pidm
 AND act.code = 'ACT'

which gives crazy results:
780	600	30
550	490	(null)
(null)	(null)	780
(null)	(null)	600
(null)	(null)	25
(null)	(null)	550
(null)	(null)	490
(null)	(null)	310

Of course, I can do this with three sub-queries and get the results that I want, but it is sloooooooooooow. Coming in at ~5 seconds. If I break it in two pieces, the SAT query only takes ~0.2 seconds and the ACT query at ~0.1.

The time is important, because this is only part of an even larger query that will go up on a web form for use. This little part by itself can't take ~5 seconds. What am I doing wrong here? Thanks for reading this long post.
Re: Self join with inner join followed by a full outer join [message #303304 is a reply to message #303300] Thu, 28 February 2008 17:01 Go to previous messageGo to next message
moshea
Messages: 51
Registered: February 2008
Location: Dublin, Ireland
Member
Hey,

You can write this query without using any self-joins etc.

Have a search for "pivot" and I think you'll find that the examples are apt to your problem here.

Additionaly you can then implement the criteria regarding which records you want (ie only ACT or SAT or ACT&SAT) in the HAVING clause of your statement.

Give it a go, and post up your attempted pivot if you have any problems.

Cheers,
Michael.

PS : Kudos on a wonderfuly presented first post. I mean, code tags, sample DDL and Inserts. Jeez, what'ca trying to do ... show everyone else up? Smile

PPS: Just did a search for "pivot" myself to see what came back. And there's a shedload of hits on other posts telling people to search for pivot! So to make it easier, I might suggest searching for "pivot decode".

[Updated on: Thu, 28 February 2008 17:04]

Report message to a moderator

icon10.gif  Re: Self join with inner join followed by a full outer join [message #303310 is a reply to message #303300] Thu, 28 February 2008 18:33 Go to previous messageGo to next message
mclovin
Messages: 2
Registered: February 2008
Junior Member
We have....

A WINNER!!
SELECT 
  pidm,
  MAX(decode(code, 'VERB', to_number(score), NULL)) verbal,
  MAX(decode(code, 'MATH', to_number(score), NULL)) math,
  MAX(decode(code, 'ACT', to_number(score), NULL)) act
FROM
  (SELECT pidm, code, score FROM myexams)
GROUP BY pidm 
HAVING 
  MAX(decode(code, 'VERB', to_number(score), NULL)) IS NOT NULL
  AND MAX(decode(code, 'MATH', to_number(score), NULL)) IS NOT NULL 
  OR MAX(decode(code, 'ACT', to_number(score), NULL)) IS NOT NULL


Thanks so much!!

On the actual data set, this query runs at ~0.2 seconds, which is a speed up of 25 times better than the version using sub-queries, and competitive with anything I was doing with the joins. Best of all, this actually gives the correct results!!

The HAVING clause is a little ugly, and I forgot for some time that I can't use the column synonyms in it, so that took a little debugging. But, I can't thank you enough. This was really kicking me in the you-know-where.
Re: Self join with inner join followed by a full outer join [message #303311 is a reply to message #303300] Thu, 28 February 2008 18:54 Go to previous messageGo to next message
moshea
Messages: 51
Registered: February 2008
Location: Dublin, Ireland
Member
Good Stuff!!

Pretty identical to what I had in mind, the only differences I might have had would be I'd prob just have selected from myexams, rather than the inline query (SELECT pidm, code, score FROM myexams). Plan-wise I think it makes no difference, so perhaps it's just a style issue. And I've no idea what the accepted wisdom is .... Confused Oh and I'd probably have an extra set of brackets in the HAVING clause (around the two AND-ed conditions) .. but thats mostly cos my brain is slow when it comes to working out the order of AND and OR's.

As for "uglyness" factor, I'm right with ya there. But like I said, the way you did it, was what jumped into my mind. Perhaps one of the resident experts might suggest a neater approach if it exists.

Cheers,
Michael.

PS : Cracking a problem which has been kicking ya in the you-know-where is what makes this game so rewarding! Smile
Re: Self join with inner join followed by a full outer join [message #303316 is a reply to message #303311] Thu, 28 February 2008 21:08 Go to previous message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
If it turns out to be 11g or if they upgrade, then you can:

-- test data:
SCOTT@orcl_11g> SELECT * FROM myexams
  2  /

      PIDM CODE SCORE
---------- ---- -----
       288 VERB 780
       288 MATH 600
       288 ACT  30
       622 ACT  25
       711 VERB 550
       711 MATH 490
       399 VERB 310

7 rows selected.


-- query:
SCOTT@orcl_11g> SELECT *
  2  FROM   myexams
  3  PIVOT  (MAX (score) FOR code IN ('VERB' AS verb, 'MATH' AS math, 'ACT' AS act))
  4  ORDER  BY pidm
  5  /

      PIDM VERB  MATH  ACT
---------- ----- ----- -----
       288 780   600   30
       399 310
       622             25
       711 550   490

SCOTT@orcl_11g> 




Previous Topic: to_date comparison problem
Next Topic: check current parameter file
Goto Forum:
  


Current Time: Fri Dec 02 22:40:19 CST 2016

Total time taken to generate the page: 0.08133 seconds