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:
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 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
- 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
- SQL for .NET Programmers–The Book - July 29th, 2013
- SQL For Programmers - Finding a String - December 4th, 2013
- SQL WHILE - SQL For Programmers - February 12th, 2014