CODEWITHSUNDEEP
sqloracle-database
Amit Baid
Amit Baid

Reputation: 137

Display records with matching column values

I have a table with following structure.

Name   Age 
----------
John   20    
Jack   20    
John   18
Steve  15
Jack   16
Lewis  17
Don    19
Jack   21
John   21
John   18

The output needs to be filtered and only displayed when Jack and John share the same age, similar to the below format. I tried using several approaches, however couldn't get the absolute result.

Name   Age 
----------
John   20    
Jack   20    
Jack   21
John   21

Upvotes: 1

Views: 80

Answers (8)

IShubh
IShubh

Reputation: 364

select distinct a.* 
from empp a  
join empp b 
on a.age=b.age 
and  a.name in ('Jack','John') 
and a.name!=b.name 
order by a.age

Upvotes: 0

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

You can simply use this :

select t1.name, t1.age from table1 t1
join table1 t2 on t1.age=t2.age and t1.name <> t2.name;

This is not limited to Jack & John only!

SQL DEMO

Upvotes: 0

nish
nish

Reputation: 1221

Try this query.

SELECT a.name, a.age
FROM (SELECT name, age FROM a) a, (SELECT name, age FROM a) b
WHERE a.name <> b.name AND a.age = b.age;

Upvotes: 2

Indent
Indent

Reputation: 4967

http://sqlfiddle.com/#!4/8093c9/11

Using a self join and compare rowid

select
    t1.name, t1.age
from
    tblNames t1
    join tblNames t2 on
        t1.rowid <> t2.rowid and        
        t1.name <> t2.name and        
        t1.age = t2.age
where
    t1.name in ('Jack','John') 
order by t1.age, t1.name;

Upvotes: 0

user7788127
user7788127

Reputation: 3

Simple ! Try this as Most Simplest 

SELECT a.name, a.age FROM xx_ages a,xx_ages b WHERE a.name <> b.name AND a.age = b.age

Upvotes: 0

DineshDB
DineshDB

Reputation: 6193

Try This: 

    SELECT * 
        FROM YourTable WHERE AGE IN
        (
        SELECT Age 
        FROM YourTable 
        GROUP BY AGE 
        HAVING COUNT(1)=2
        )D
    ORDER BY AGE

Upvotes: 0

jarlh
jarlh

Reputation: 44795

Have a cte that returns only Jack and John rows. Do a self join:

with cte as
(
    select name, age
    from tablename
    where name in ('Jack','John')
)
select t1.*
from cte t1
join cte t2 on t1.name <> t2.name and t1.age = t2.age

Upvotes: 0

Ullas
Ullas

Reputation: 11556

Use a SELF JOIN and get the rows having different names and same age with only has names John and Jack.

Query

select t1.name, t1.age
from your_tabel_name t1
join your_tabel_name t2
on t1.name <> t2.name
and t1.age = t2.age
where t1.name in ('Jack','John')
group by t1.name, t1.age
order by t1.age, t1.name;

Here is a sql fiddle demo.

Upvotes: 0

Related Questions