Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Need SQL Query help in SQL Server

Posted on 2009-12-18
4
Medium Priority
?
264 Views
Last Modified: 2012-05-08
Hi,

I have the following table and data:

-- Create a table

CREATE TABLE dbo.Students
(
   Sid          int,
   Name      varchar(128),
   Degree   varchar(128)
)
GO

-- Insert some data

INSERT INTO Students VALUES(1, 'Mike', 'B.Sc')
GO

INSERT INTO Students VALUES(1, 'Mike', 'B.A')
GO

INSERT INTO Students VALUES(2, 'Milani', 'B.Com')
GO

INSERT INTO Students VALUES(3, 'Sara', 'B.A')
GO

INSERT INTO Students VALUES(4, 'Bob', 'B.Sc')
GO

INSERT INTO Students VALUES(4, 'Bob', 'B.A')
GO

INSERT INTO Students VALUES(5, 'Kris', 'B.Com')
GO

INSERT INTO Students VALUES(5, 'Kris', 'B.Sc')
GO

/*
  Need a query to display the records who have studied both 'B.Sc' and 'B.A'
*/

Example : Mike, Bob records needs to be showup.

Thanks
 
0
Comment
Question by:milani_lucie
  • 4
4 Comments
 
LVL 61

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 26085201
try this

select name
from Students
where degree in ('B.Sc', 'B.A')
group by name
having count(*)=2
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26085219
or this

select name
from students
group by name
having sum(case when degree in ('B.Sc', 'B.A') then 1 else 0 end)=2
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26085225
or this

select distinct name
from students s
where (select count(1) from students s1 where s.name=s1.name and degree in ('B.Sc', 'B.A')) = 2
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26085226
;) selection is yours...
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question