SQL – Filtering WHERE condition on two rows

iStock_000005792139Medium I received the following question a couple of days ago from a programmer using the “Ask  a Question” form which you can access from the menu of this blog:

Given the following table:

Name Language
Nikhil Hindi
Nikhil English
Kisu Hindi
Kisu English
Rakesh Hindi
Kousik Bangali

How do I select names of persons who know both Hindi and English?

In this table, that query should return Nikhil and Kisu.

Normally, if it were me, I’d create a Language table and a Name table and use an intermediate table to join them.  But the solution would still be about the same.  So we’ll work with the table we’ve been given.

What we need to do here is make this look like it is two tables–The Hindi table and the English table–and do a JOIN between the two:

SELECT     NameLang.Name
FROM         NameLang INNER JOIN
NameLang AS NameLang_1 ON NameLang.Name = NameLang_1.Name
WHERE     (dbo.NameLang.Language = ‘English’) AND (NameLang_1.Language = ‘Hindi’)

This pulls all of the names from the table where the person speaks English and then JOINS them to a selection of all the names of people who speak Hindi.  What you get back is a list of names of all the people who speak both.

Related Post

Leave a Reply

Comment Policy:

  • You must verify your comment by responding to the automated email that is sent to your email address. Unverified comments will never show.Leave a good comment that adds to the conversation and I'll leave your link in.
  • Leave me pure spam and I'll delete it.
  • Leave a general comment and I'll remove the link but keep the comment.

Notify me of followup comments via e-mail