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?
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.

PortletPaulfreelancerCommented:
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

0

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 ChongCommented:
what do you mean by >>  to limit the sql process ?
0
Vikas GargBusiness Intelligence DeveloperCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
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
0
PortletPaulfreelancerCommented:
<>  "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
0
Enrique Gomez EstebanConsultantAuthor Commented:
I've requested that this question be deleted for the following reason:

The response was good, anyway.
0
Ryan ChongCommented:
pls give a valid reason to delete this question
0
PortletPaulfreelancerCommented:
that is not a valid reason
0
Ryan ChongCommented:
@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.
0
Enrique Gomez EstebanConsultantAuthor Commented:
Thank for your recommendations. I could not check it properly.
0
PortletPaulfreelancerCommented:
@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.
0
Ryan ChongCommented:
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.
0
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.
0
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.
0
Enrique Gomez EstebanConsultantAuthor Commented:
OK
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.