CODEWITHSUNDEEP
mysqlsqldatabase
Grevak
Grevak

Reputation: 543

How can I get a record which has two corresponding columns which are multiple in SQL?

For example, I have two tables:

ID | Name
------------
1  | test 1 
2  | test 2 

ID2| ID | Age
--------------
1  | 1  | 18
2  | 1  | 18
3  | 1  | 19
4  | 2  | 18
5  | 2  | 19

I want to have all records that have columns which are multiple in name with age but I don't know how to do that.

I want an output like this:

Name     | Age
--------------------
test 1   | 18
test 1   | 18

Can anyone help me?

Upvotes: 0

Views: 77

Answers (3)

LukStorms
LukStorms

Reputation: 29667

You can also use an IN on tupples.

And a GROUP BY can be combined with a HAVING to only get those that have duplicate (name, age).

SELECT t1.Name, t2.Age
FROM YourTable2 t2
LEFT JOIN YourTable1 t1 ON t1.ID = t2.ID
WHERE (t2.ID, t2.Age) IN (
      SELECT ID, Age
      FROM YourTable2
      GROUP BY ID, Age
      HAVING COUNT(*) > 1
  );

Upvotes: 0

Harshil Doshi
Harshil Doshi

Reputation: 3592

Try following query:

Select t1.*, t2.* 
from table1 t1
join table2 t2
on  t1.id = t2.id
join (select id, age
      from table2
      group by id, age
      having count(*) > 1
     ) t3
on t1.id = t2.id and t2.id = t3.id and t2.age = t3.age

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Use exists:

select t.*
from t
where exists (select 1
              from t t2
              where t2.name = t.name and t2.age = t.age and
                    t2.id <> t.id
             );

With an index on (name, age, id), this should be the fastest approach.

Upvotes: 0

Related Questions