CODEWITHSUNDEEP
oracle-databaseoracle11g
Mong2203
Mong2203

Reputation: 71

combine unrelated table with different column numbers (oracle 11g)

First query:

SELECT f.availableID, f.lecturerID,
ex.lecturerID AS Examiner, ex.lecturerFullname,
v.availableID, v.availableDay, v.availableStart, v.availableEnd, v.availableEnd
FROM free f
INNER JOIN lecturer ex
ON f.lecturerID = ex.lecturerID
INNER JOIN availability v
ON f.availableID = v.availableID

Second query:

SELECT s.studentID, s.studentName, s.projectTitle, s.lecturerID AS supervisor,
sv.lecturerID, sv.lecturerFullname
FROM student s 
INNER JOIN lecturer sv
ON s.lecturerID = sv.lecturerID

I would like to combine these two queries. SO i tried UNION but it didn't work due to not equal number of columns. Plus, these two queries have INNER JOIN which makes me more confused.

I would like to query these information WHERE by using ex.availableID.

This queries are meant to find an examiner availableID but I need to JOIN with student and supervisor where they are not related to each other.

Expected outcome (these are the important columns I desired, I ignored the other duplicate column to ease the understanding) :

studentID   studentName   projectTitle   supervisor   examiner   availableID
  123          hunter         abc           mary        kent        10

Database scheme:

Free:

availableID (number)
lecturerID (varchar2)

Lecturer:

lecturerID (varchar2)
lecturerFullname (varchar2)

Availability:

availableID (number)
availableDay (varchar2)
availableStart (Date) //Time
availableEnd (Date)   //Time
availableDate (Date)

Student:

studentID (varchar2)
studentName (varchar2)
Projecttitle (varchar2)
lecturerID(varchar2)

lecturer table will be used twice in my case as supervisor and examiner. Would be nice if i can combine them into one select statement :)

UPDATE I have updated my query a little bit according to requirement :

SELECT s.studentID,   s.studentName, s.projectTitle, sv.lecturerFullname AS supervisor,
ex.lecturerfullname AS examiner, f.availableID, f.lecturerID, 
v.availableID, v.availableday, v.availablestart, v.availableend, v.availableend
FROM student s
INNER JOIN lecturer sv
  ON s.lecturerID = sv.lecturerID
CROSS JOIN (free f
  INNER JOIN lecturer ex
    ON f.lecturerID = ex.lecturerID
  INNER JOIN availability v
    ON f.availableID = v.availableID)
WHERE f.lecturerID = '1009'
AND s.lecturerID = '1027'
AND s.studentID = '2013366609';

Its working fine in db.. But as i used it in JAVA, it gives me an exception : java.sql.SQLException: ORA-06553: PLS-306: wrong number or types of arguments in call to 'V'

Upvotes: 0

Views: 76

Answers (1)

Alessandro Vecchio
Alessandro Vecchio

Reputation: 186

the UNION operator is not what you need here. As you said, there is no relation between the two queries you have posted so, perhaps a CROSS JOIN would get what you need. But, if the tables are big, you may want to add some more condition to filter the result. It should be something like that:

SELECT s.studentID,   s.studentName, s.projectTitle, sv.lecturerFullname AS supervisor, ex.lecturer AS examiner, f.availableID
FROM student s
INNER JOIN lecturer sv
  ON s.lecturerID = sv.lecturerID
CROSS JOIN (free f
  INNER JOIN lecturer ex
    ON f.lecturerID = ex.lecturerID
  INNER JOIN availability v
    ON f.availableID = v.availableID)
WHERE f.availableID = 10;

Upvotes: 0

Related Questions