Find two employees having the same designation attribute - in SQL using self joins

Question

Display records having the same designation as "Peter" using inner join

CONDITION : without directly using the designation of Peter in the WHERE CLAUSE.

Answer 1

I guess technically since you have CONDITION : without directly using the designation of Peter in the WHERE CLAUSE. you could use:

SELECT *
FROM mytable t1 
INNER JOIN mytable t2 ON t1.condition = t2.condition
    AND t2.name = 'Peter'

You have records matching Peter's without using "Peter" in a WHERE clause.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table1 (name varchar(10),  condition1 varchar(10));

INSERT INTO table1 (name, condition1)
SELECT 'Peter', 'c1' FROM dual UNION ALL
SELECT 'Paul', 'c2' FROM dual UNION ALL
SELECT 'Mary', 'c1' FROM dual UNION ALL
SELECT 'Jay', 'c2' FROM dual UNION ALL
SELECT 'SilentBob', 'c1' FROM dual UNION ALL
SELECT 'Bill', 'c1' FROM dual UNION ALL
SELECT 'Ted', 'c4' FROM dual UNION ALL
SELECT 'Rufus', 'c3' FROM dual
;

Query 1:

SELECT *
FROM table1 t1
INNER JOIN table1 t2 ON t1.condition1 = t2.condition1
  AND t2.name = 'Peter'

Results:

|      NAME | CONDITION1 |  NAME | CONDITION1 |
|-----------|------------|-------|------------|
|     Peter |         c1 | Peter |         c1 |
|      Mary |         c1 | Peter |         c1 |
| SilentBob |         c1 | Peter |         c1 |
|      Bill |         c1 | Peter |         c1 |