ORACLE INNER JOIN

Hi
Can I use a WHERE clause to limit the sql process? I means:


select  empno, ename
from emp p1
where deptno not = 12
inner join
(select empno, max(empno)
from emp p2
group by empno) grouemp
ON  p1.empno= gouemp.empno
Enrique Gomez EstebanConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
not like you have it

This will work 
SELECT
      empno
    , ename
FROM emp p1
INNER JOIN (
      SELECT
            empno
          , MAX(empno)
      FROM emp p2
      GROUP BY empno
) grouemp
      ON p1.empno = gouemp.empno
WHERE deptno <> 12

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
what do you mean by >>  to limit the sql process ?
Vikas GargAssociate Principal EngineerCommented:
Hi You can do like this

select  empno, ename
from (select * from emp where deptno != 12)  p1

inner join
(select empno, max(empno)
from emp p2
group by empno) grouemp
ON  p1.empno= gouemp.empno
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Get Your eBook
PortletPaulEE Topic AdvisorCommented:
mmm

but the subquery makes very little sense.

if you group by empno, then max(empno) is the same value

table emp
empno
----------
1
2
3

      SELECT
            empno
          , MAX(empno)
      FROM emp p2
      GROUP BY empno

empno max(empno)
----------  -----------------
1            1
2            2
3            3
4            4

so, it does not achieve much

Can you provide these?
a. "sample data"
b. "expected result"

then we can suggest a query for you
PortletPaulEE Topic AdvisorCommented:
<>  "NOT EQUAL"

12 <> 13
'a' <> 'fred'

You can also use this syntax

!=

12 != 13
'a' != 'fred'

<> is available in all SQL variants,
!= is only for some vendors
Enrique Gomez EstebanConsultantAuthor Commented:
I've requested that this question be deleted for the following reason:

The response was good, anyway.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
pls give a valid reason to delete this question
PortletPaulEE Topic AdvisorCommented:
that is not a valid reason
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
@Enrique Gomez Esteban,

can you elaborate further, or post your solution here (if there is any), or as mentioned, pls provide reason to close this question.
Enrique Gomez EstebanConsultantAuthor Commented:
Thank for your recommendations. I could not check it properly.
PortletPaulEE Topic AdvisorCommented:
@Enrique Gomez Esteban,

You should not just choose any comment at random, ID: 41001026 does not offer a solution to your original question.

If your problem was solved by expert comments BEFORE ID: 41001025 then you should choose one or more of those comments.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Agreed with Paul on the principle of accepting a comment as the answer. Am already put a comment earlier in the "request an attention" relating to this issue.
Enrique Gomez EstebanConsultantAuthor Commented:
Sorry for any incovenience.  I opened this question and I got several helpful anwers but I could not check it properly at the moment so the question remains opened several months. I just was trying to close it. Thanks for your support.
Enrique Gomez EstebanConsultantAuthor Commented:
Well,
I think I have already explained that that the question was not enough detailed and of course the answer  also lack of this detail. As it was my fault because this  issue that was very specifi c was removed from the backlog  I could not give the right detail.  Thats is why I tried to DELETE the question  (sure I did something wrong) and  I insist on the question unfortunately was not detailed properly and of course the answer  is not detailed enough or is not addressing the right question and documentation.
That's is whiy I think a grade=B is right.
Enrique Gomez EstebanConsultantAuthor Commented:
OK
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.