Select same last names but not same names

Question

I have a table with fname|lname|startyear|endyear

Take it that a person with same fname and lname is a unique person. There can be multiple entries with the same fname|lname.

1)How do i find all the same last names belonging to different people?

Eg 'tom' |'jerry'|1990|1991|

'vlad' |'jerry'|1991|1992|

'tim' |'cook' |1991|1992|

'tim' |'cook' |1992|1993|

Output:

jerry

2)Which people (first and last names) served between 'Mary' 'Jane's two terms?

Eg 'mary' |'jane'|1989|1990|

'tom' |'jerry'|1990|1991|

'vlad' |'jerry'|1991|1992|

'tim' |'cook' |1991|1992|

'tim' |'cook' |1992|1993|

'mary' |'jane'|1993|1994

Output

tom jerry

vlad jerry

tim cook

Always include the specific database you are using when asking SQL questions. — sstan

Teja Teja · Accepted Answer · 2016-09-15T01:47:03

1) In this below query, the inline view gets you all the unique combination of fname,lname's and its joined with the original table on lname that will give you all the unique lnames but have multilple first names.

SELECT lname
  FROM table t1
INNER JOIN
    ( SELECT fname,lname
        FROM table 
       GROUP BY fname,lname
       HAVING COUNT(1) = 1 
     ) t2
 ON t1.lname = t2.lname;

2) In this query, the inline view will return the min year and max year of the terms served by Mary Jane and then its cross joined to the original table and the comparison is done on the startyear and endyear which will give you all the fname,lname's who served in between Mary Jane.

 SELECT fname,lname
   FROM table t1
  CROSS JOIN 
  ( SELECT MIN(startyear) AS minstart,MAX(endyear) AS maxend
      FROM table 
     WHERE fname = 'Mary' AND lname = 'Jane'
  ) t2
 WHERE t1.startyear >= t2.minstart AND t1.endyear <= t2.maxstart;

Select same last names but not same names

1 Answers