ORACLE INNER JOIN

Enrique Gomez Esteban
Enrique Gomez Esteban used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
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

Ryan ChongSoftware Team Lead

Commented:
what do you mean by >>  to limit the sql process ?
Vikas GargAssociate Principal Engineer
Top Expert 2014

Commented:
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
Ensure you’re charging the right price for your IT

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

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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 Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
<>  "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

Author

Commented:
I've requested that this question be deleted for the following reason:

The response was good, anyway.
Ryan ChongSoftware Team Lead

Commented:
pls give a valid reason to delete this question
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
that is not a valid reason
Ryan ChongSoftware Team Lead

Commented:
@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.

Author

Commented:
Thank for your recommendations. I could not check it properly.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
@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 ChongSoftware Team Lead

Commented:
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.

Author

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.

Author

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.

Author

Commented:
OK

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial