Home » SQL For Programmers » SQL – Filtering WHERE condition on two rows

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:


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.

Like this Article? Subscribe to get every article sent to your email.

Related Post

  • SQL For Programmers – Stored Procedure BasicsSQL For Programmers – Stored Procedure Basics Now that we've established a reason for using them, let's continue with the basic syntax of the stored procedure. I think you'll find that it is not that dissimilar to writing a procedure […]
  • Basic SQL Commands – SQL For ProgrammersBasic SQL Commands – SQL For Programmers Before we get into the specifics of the commands that can be used within a stored procedure, I think it would be helpful to review some of the more basic commands that we can use that […]
  • Random in SQL – SQL For ProgrammersRandom in SQL – SQL For Programmers Last week I had an interesting assignment: Given a specific record, randomly retrieve three related records from the database.Naturally, as a programmer, I started looking for the SQL […]
  • SQL WHILE – SQL For ProgrammersSQL WHILE – SQL For Programmers The IF statement we looked at on Tuesday was pretty tame compared to the WHILE construct.Actually, the main thing you need to keep in mind is that WHILE is all you have.  There is no […]
  • SQL CURSOR – SQL For ProgrammersSQL CURSOR – SQL For Programmers One of the things it took me a while to figure out was how to get a stored procedure to loop through data in a table and pull out specific data I needed.There are actually two parts […]

About Dave Bush

Dave Bush is a Full Stack ASP.NET developer. His commitment to quality through test driven development, vast knowledge of C#, HTML, CSS and JavaScript as well as his ability to mentor younger programmers and his passion for Agile/Scrum as defined by the Agile Manifesto and the Scrum Alliance will certainly be an asset to your organization.