How can I display my primary key as a column when using inner join?

select StaffNo, SName, BranchName from STAFF  inner join BRANCH on staff.branchno = Branch.branchno;

I am trying to display BranchNo in the results from the above query, whenever I add BranchNo after branch name like this.

select StaffNo, SName, BranchName, Branchno from STAFF  inner join BRANCH on staff.branchno = Branch.branchno;

I get this error code ORA-00918: column ambiguously defined. To my understanding it is something to do with the fact that BranchNo is my primary key, is there a way I can get around this and display BranchNo in the query results? Many thanks.

Answer

Just prefix the ambiguous column name with the table it belongs to.

Actually, you should always prefix the columns in multi-table queries with the relevant table names; this makes the queries unambiguous for your database and for the readers (and future maintainers) of the query.

For this, table aliases come handy (they make the query shorter).

Here is your updated query – I made a few assumptions on from which table each column comes from, that you might need to review:

select 
    s.staffno, 
    s.sname, 
    b.branchname,
    s.branchno
from staff s 
inner join branch b on s.branchno = b.branchno;