SQL – Filtering WHERE condition on two rows
November 26th, 2008 | 
Author: 
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.
Other post in SQL For Programmers
- SQL For Developers - 9 Reasons to bother - August 13th, 2008
- MSSQL CREATE and DROP Database - SQL for Programmers - August 20th, 2008
- MSSQL CREATE TABLE - SQL For Programmers - August 22nd, 2008
- SQL For Programmers - ALTERing the TABLE - September 1st, 2008
- SQL For Programmers - Finding a String - September 3rd, 2008
- SQL For Programmers - Finding IN a List - September 8th, 2008
- SQL For Programmers - Stored Procedure Basics - September 16th, 2008
- Basic SQL Commands - SQL For Programmers - September 18th, 2008
- SQL IF/WHILE Blocks - SQL For Programmers - September 30th, 2008
- SQL WHILE - SQL For Programmers - October 2nd, 2008
- Temporary Tables - SQL For Programmers - October 7th, 2008
- SQL CURSOR - SQL For Programmers - October 13th, 2008
- SQL CURSOR Performance - SQL For Programmers - October 22nd, 2008
- Random in SQL - SQL For Programmers - November 4th, 2008
- SQL - Filtering WHERE condition on two rows - November 26th, 2008
- SQL - Transactions - April 15th, 2009
- SQL For Programmers – New Question - July 6th, 2009
- SQL SELECT CASE Instead of IIF - October 20th, 2009
- SQL For Programmers - Stored Procedures (Better than LINQ) - August 28th, 2012
Related Post
SQL 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 in any o...
Basic 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 don't really ...
Random 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 random f...
SQL 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 such thing...
SQL 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 to this qu...
Posted in 
Tags: 