CODEWITHSUNDEEP
oracle-databasesqlplus
Danyal Baig
Danyal Baig

Reputation: 1

Trouble With Basic Query Searching

Not sure if the this is the right place to ask but i have a question with beginner sql.

I have the table dept and emp which include:

SQL> desc dept;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------

 DEPTNO                                             NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                       NOT NULL VARCHAR2(13)

SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------

 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

I need to search for all jobs that are in department 30 and to Include the location of department 30.

What im trying is this:

SQL> select emp.job, dept.deptno, dept.loc
  2  from emp, dept
  3  where emp.deptno = dept.deptno
  4  where deptno = '30';
where deptno = '30'
*
ERROR at line 4:
ORA-00933: SQL command not properly ended

But as you can see its not working and i have tried different variations but still no luck. Am i on the right track? How would I solve this?

Upvotes: 0

Views: 19

Answers (1)

Justin Cave
Justin Cave

Reputation: 231681

It sounds like you want something like this. When you have multiple conditions in the where clause, you only specify where once and combine them with and or or conditions.

select emp.job, dept.deptno, dept.loc
 from emp, dept
where emp.deptno = dept.deptno
  and dept.deptno = 30;

Unless there is some reason that you really need to use the old join syntax, you probably ought to start with the SQL 99 syntax. It makes it much easier to move between databases, it makes your queries easier to read by separating join and filter conditions, and it makes life much easier when you start working on outer joins.

select emp.job, dept.deptno, dept.loc
 from emp 
      join dept
        on( emp.deptno = dept.deptno )
where dept.deptno = 30;

Upvotes: 1

Related Questions