Home » SQL & PL/SQL » SQL & PL/SQL » intersection
intersection [message #224922] Thu, 15 March 2007 23:08 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Dear all

I have a table given below.

EX > create table ds
  2  (
  3  slot1 varchar2(5),
  4  slot2 varchar2(5)
  5  )
  6  /

Table created.


EX > insert into ds values('MT102','ES341');

1 row created.

EX > insert into ds values('MT101','CS322');

1 row created.

EX > insert into ds values(NULL,'EE371');

1 row created.

EX > select * from ds;

SLOT1 SLOT2
----- -----
MT102 ES341
MT101 CS322
      EE371

EX > 


I need to find out count of reg_no of those students reading both the courses. By comparing each value in slot1 with that of slot2. The result should be as below.

SLOT1 SLOT2  CLASH
MT102 ES341  0
MT102 CS322  2
MT102 EE371  10
MT101 ES341  0
MT101 CS322  2
MT101 EE371  0


The student registered courses table is below
EX > desc src
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ---------------------
 REG_NO                                                         NUMBER(8)
 SEMESTER_CODE                                                  NUMBER(1)
 SEMESTER_YEAR                                                  NUMBER(4)
 GRADECHARVALUE                                                 CHAR(2)
 OPTIONAL                                                       NUMBER(1)
 COURSE_SEQ_NO                                                  NUMBER
 COURSE_CODE                                                    VARCHAR2(20)
 COURSE_USAGE_STATUS                                            NUMBER(1)
 COURSE_ADW                                                     NUMBER(1)
 REMARKS                                                        VARCHAR2(80)
 REPEAT                                                         CHAR(6)
 TEMPCOLFAC                                                     CHAR(6)




Currently I am using the following query to find out the intersected students. This takes a lot of time as I have to pass a lot of values manualy.
 select reg_no from src
 where course_code='&CC1'
 and semester_code=1 and semester_year=2007
 INTERSECT
 select reg_no from src
 where course_code='&CC2'
 and semester_code=1 and semester_year=2007


Please help me in this regard.

Thank you,

rzkhan



Re: intersection [message #224992 is a reply to message #224922] Fri, 16 March 2007 07:45 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member

SELECT a.cc1 Slot1,
b.cc2 Slot2,
COUNT(* )
FROM (SELECT reg_No,
Course_Code cc1
FROM src
WHERE Course_Code IN (SELECT Slot1
FROM ds)) a,
(SELECT reg_No,
Course_Code cc2
FROM src
WHERE Course_Code IN (SELECT Slot2
FROM ds)) b
WHERE a.reg_No = b.reg_No
GROUP BY a.cc1,
b.cc2
Re: intersection [message #225059 is a reply to message #224922] Fri, 16 March 2007 17:23 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
I'm not sure that this does exactly what you want because I could not fully understand your problem description, but here are few options which, in some form, could do what you need
SQL> SELECT *
  2  FROM src;

    REG_NO COURSE_CODE SEMESTER_CODE SEMESTER_YEAR
---------- ----------- ------------- -------------
         1 a                       1          2007
         2 a                       1          2007
         3 a                       1          2007
         1 b                       1          2007
         3 b                       1          2007
         5 b                       1          2007
         5 c                       1          2007
         6 c                       1          2007
         7 c                       1          2007

9 rows selected
This is your code
SQL> SELECT reg_no
  2  FROM src
  3  WHERE course_code = 'a'
  4  AND semester_code = 1
  5  AND semester_year = 2007
  6  INTERSECT
  7  SELECT reg_no
  8  FROM src
  9  WHERE course_code = 'b'
 10  AND semester_code = 1
 11  AND semester_year = 2007;

    REG_NO
----------
         1
         3
This should give you the same results, only faster
SQL> SELECT reg_no
  2  FROM src
  3  WHERE course_code IN ('a', 'b')
  4  GROUP BY semester_code, semester_year, reg_no
  5  HAVING COUNT(*) > 1;

    REG_NO
----------
         1
         3
If you need more information from the rows, you could use this
SQL> SELECT *
  2  FROM (SELECT src.*,
  3               COUNT(*) OVER(PARTITION BY semester_code, semester_year, reg_no) cnt
  4        FROM src
  5        WHERE course_code IN ('a', 'b'))
  6  WHERE cnt > 1;

    REG_NO COURSE_CODE SEMESTER_CODE SEMESTER_YEAR        CNT
---------- ----------- ------------- ------------- ----------
         1 a                       1          2007          2
         1 b                       1          2007          2
         3 a                       1          2007          2
         3 b                       1          2007          2

These two have the advantage of not being limited to just two course_codes per query
SQL> SELECT reg_no
  2  FROM src
  3  GROUP BY semester_code, semester_year, reg_no
  4  HAVING COUNT(*) > 1;

    REG_NO
----------
         5
         1
         3

SQL> SELECT *
  2  FROM (SELECT src.*,
  3               COUNT(*) OVER(PARTITION BY semester_code, semester_year, reg_no) cnt
  4        FROM src)
  5  WHERE cnt > 1;

    REG_NO COURSE_CODE SEMESTER_CODE SEMESTER_YEAR        CNT
---------- ----------- ------------- ------------- ----------
         1 b                       1          2007          2
         1 a                       1          2007          2
         3 a                       1          2007          2
         3 b                       1          2007          2
         5 b                       1          2007          2
         5 c                       1          2007          2

6 rows selected
Re: intersection [message #225074 is a reply to message #224922] Fri, 16 March 2007 23:32 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member

have you solved your problem
Re: intersection [message #225184 is a reply to message #225074] Sun, 18 March 2007 23:19 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Thank you all for your kind efforts. I have solved my problem as below..

  1  SELECT a.course_code,b.course_code, COUNT (a.reg_no) AS "student_reading_both_courses"
  2  FROM
  3  (SELECT src.course_seq_no, src.reg_no,fcp.course_code
  4   FROM   src, fcp
  5   WHERE  src.course_seq_no = fcp.course_seq_no
  6   and  src.semester_code=1
  7   and src.semester_year=2007
  8   and src.course_code IN('EE231','ME362','ME469','MM212')) a,
  9  (SELECT src.course_seq_no, src.reg_no, fcp.course_code
 10   FROM   src, fcp
 11   WHERE  src.course_seq_no = fcp.course_seq_no
 12   and src.semester_code=1
 13   and src.semester_year=2007
 14   and src.course_code IN('MT101','MT102')) b
 15  WHERE  a.reg_no = b.reg_no
 16  GROUP  BY a.course_code, b.course_code
 17* ORDER  BY a.course_code, b.course_code
EX > /

COURSE_C COURSE_C student_reading_both_courses
-------- -------- ----------------------------
EE231    MT101                               1
EE231    MT102                               5
MM212    MT101                               1
MM212    MT102                               2



and this is what I needed..

Thanks

rzkhan
Re: intersection [message #225365 is a reply to message #224922] Mon, 19 March 2007 14:24 Go to previous message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
try something like this

select a.reg_no
from src a,src b
where a.course_code='&CC1'
and a.semester_code=1 and a.semester_year=2007
and b.course_code='&CC1'
and b.semester_code=1 and b.semester_year=2007
and a.reg_no = b.reg_no;
Previous Topic: Login as a different schema in the middle of a procedure
Next Topic: Whats wrong with these joins? Please help !!!!
Goto Forum:
  


Current Time: Sun Dec 04 09:00:26 CST 2016

Total time taken to generate the page: 0.09676 seconds