Need help writing a query Oracle SQL

Question

write a query to show the staffid, charge code, specialty id, specialty name, date consulted, patient number, and due date will be calculated using date consulted + 21

SELECT
    staffid, 
    chrgcode,
    dateconsulted,
    patientno,
    (dateconsulted + 21) "Due Date"
FROM
    staff_speciality,
    staff,
    speciality,
    consultation;

SELECT staffid, chrgcode, dateconsulted, patientno, (dateconsulted + 21) "Due >Date"
*
ERROR at line 1:
ORA-00918: column ambiguously defined
Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
SQL> describe speciality
Name Null? Type

SPECID NOT NULL CHAR(4)
SPECNAME VARCHAR2(25)
SPECNOTES VARCHAR2(100)
SQL> describe staffid
ERROR:
ORA-04043: object staffid does not exist
SQL> describe staff
Name Null? Type

STAFFID NOT NULL CHAR(2)
FIRSTNAME VARCHAR2(20)
LASTNAME VARCHAR2(20)
ROLE VARCHAR2(15)
GENDER CHAR(1)
DATEJOINED DATE
DATELEFT DATE SQL> describe staffspeciality ERROR:
ORA-04043: object staffspeciality does not exist
SQL> describe staff_speciality
Name Null? Type

STAFFID NOT NULL CHAR(2)
SPECID NOT NULL CHAR(4)
DATEQUALIFIED NOT NULL DATE
VALIDTILLDATE DATE
DETAILS VARCHAR2(100)
SQL> describe consultation Name Null? Type

CHRGCODE NOT NULL CHAR(7) STAFFID NOT NULL CHAR(2) DATECONSULTED NOT NULL DATE STARTTIME NUMBER(4,2)
PATIENTNO CHAR(7)
SQL> SELECT staffid, chrgcode, dateconsulted, patientno, (dateconsulted + 21) "Due Date"
2 FROM staff_speciality, staff, speciality, consultation;
SELECT staffid, chrgcode, dateconsulted, patientno, (dateconsulted + 21) "Due Date"
*
ERROR at line 1:
ORA-00918: column ambiguously defined

Hint: JOIN. ON. Never use commas in the FROM clause. — Gordon Linoff
and alias the columns with the related table's aliases, such as SELECT s.staffid .... from staff_speciality ss join staff s on s.staffid = ss.staff_id .... — Barbaros Özhan
Could you show an example I don't know how to do that. Or could you link a website that could help me with that please? — Reece
@BarbarosÖzhan I don't understand that at all.... :( why are you adding s. to staffid? what is ss? Why are you making s.staffid = ss.staff_id? — Reece

Barbaros Özhan Barbaros Özhan · Accepted Answer · 2019-05-25T00:09:56

You can construct such a query with ANSI-92 syntax

SELECT s.staffid, chrgcode, dateconsulted, patientno, (dateconsulted + 21) "Due Date"
  FROM staff_speciality ss 
  JOIN staff s ON s.staffid = ss.staffid
  JOIN speciality sp ON sp.specid = ss.specid
  JOIN consultation c ON c.staffid = s.staffid

where especially important to alias the staffid column in the select list ( you can also alias every columns explicitly ), since three tables have that as common column, and if not specified, Oracle cannot know which table's column is chosen without an alias, and ORA-00918: column ambiguously defined is raised due to this mistake.

Need help writing a query Oracle SQL

1 Answers