Home » SQL & PL/SQL » SQL & PL/SQL » join query
join query [message #193714] Tue, 19 September 2006 02:10 Go to next message
gtriant
Messages: 41
Registered: September 2006
Member
Hi!
I want to select a column, which exists in two tables and also perform some checks at the where clause.
While the table_name.column_name approach works on the where clause, it doesn't seem to work after the select (i get an error message that says that there is no such column name).
Below i am giving some queries that don't work for reasons i cant't tell.
(suppose two tables employee and department and suppose that i want to see the dep_num column which exists in both)


SELECT EMPL_NAME, DEP_NUM FROM EMPLOYEE e DEPARTMENT d
WHERE E.DEP_NUM=D.DEP_NUM
(error:ORA 00918 column ambiguously degined)

SELECT EMPLOYEE.EMPL_NAME, EMPLOYEE.DEP_NUM FROM EMPLOYEE e DEPARTMENT d
WHERE E.DEP_NUM=D.DEP_NUM
(error:ORA 00904: invalid column name)

The actual query is much more complicated but i think that this is the heart of the problem. Of course there is nothing i can do about the column names.

Any help would be most appreciated
Re: join query [message #193718 is a reply to message #193714] Tue, 19 September 2006 02:27 Go to previous messageGo to next message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

You Wrote
Quote:

SELECT EMPL_NAME, DEP_NUM FROM EMPLOYEE e DEPARTMENT d
WHERE E.DEP_NUM=D.DEP_NUM
(error:ORA 00918 column ambiguously degined)

SELECT EMPLOYEE.EMPL_NAME, EMPLOYEE.DEP_NUM FROM EMPLOYEE e DEPARTMENT d
WHERE E.DEP_NUM=D.DEP_NUM
(error:ORA 00904: invalid column name)


The answer for you both tries is

SELECT E.EMPL_NAME, D.DEP_NUM FROM EMPLOYEE e, DEPARTMENT d
WHERE E.DEP_NUM=D.DEP_NUM

or

SELECT E.EMPL_NAME, E.DEP_NUM FROM EMPLOYEE e, DEPARTMENT d
WHERE E.DEP_NUM=D.DEP_NUM

in first your try you was getting error because both of tables i.e. Employee & Department have same named column DEP_NUM. So You need to mention from which table you want to select that column in select clause.

In second try you have defined a alias to table EMPLOYEE so you need to call the columns according to your alias.

Sandy
Re: join query [message #193720 is a reply to message #193714] Tue, 19 September 2006 02:30 Go to previous message
gtriant
Messages: 41
Registered: September 2006
Member
never mind. as i see i must use the table's short name next to the select. it doesn't makes mush sense though.
Sorry for the post, but i was really stuck for a bit yesterday!
Previous Topic: E.F.COdd
Next Topic: Question in group by clause
Goto Forum:
  


Current Time: Fri Dec 09 02:25:42 CST 2016

Total time taken to generate the page: 0.15200 seconds