Oracle

 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me



Go Back   Tutorialized ForumsDatabasesOracle

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Tutorialized Forums Sponsor:
  #1  
Old October 23rd, 2008, 08:41 AM
allasyed allasyed is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Oct 2008
Posts: 1 allasyed User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 13 m 57 sec
Reputation Power: 0
Angry Query

i have one table students

stud_name part_sport

Raju football
kumar cricket
Raju hockey
Balu vollybal
Mahesh hockey
Rajesh cricket

In this i want a record who are the students participating in both football and hockey only.

Result is raju is the only person participatin in football and hockey.

i want oracle query for this condition.


pls anyone help me

Reply With Quote
  #2  
Old April 1st, 2009, 01:16 AM
ora-cle ora-cle is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Mar 2009
Posts: 2 ora-cle User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 33 m 12 sec
Reputation Power: 0
Here it is

Not sure if you have your tables normalized if not you need to normalize your tables and have one for student one for sport and one for student_sport.

Anyways here is how you would get the result

you will need 3 queries
first create a view with all students who play football
create another view who play hockey
then join the two views on the student name you will have your result

there might be other ways as well to get the same result

Reply With Quote
  #3  
Old April 1st, 2009, 11:52 PM
ora-cle ora-cle is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Mar 2009
Posts: 2 ora-cle User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 33 m 12 sec
Reputation Power: 0
Here you go

SELECT students.stud_name
FROM students
WHERE student.part_sport = 'hockey'
INTERSECT
SELECT students_stud_name
FROM students
WHERE student.part_sport = 'football';

Reply With Quote
  #4  
Old April 1st, 2009, 11:57 PM
ora-cle ora-cle is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Mar 2009
Posts: 2 ora-cle User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 33 m 12 sec
Reputation Power: 0
Quote:
Originally Posted by allasyed
i have one table students

stud_name part_sport

Raju football
kumar cricket
Raju hockey
Balu vollybal
Mahesh hockey
Rajesh cricket

In this i want a record who are the students participating in both football and hockey only.

Result is raju is the only person participatin in football and hockey.

i want oracle query for this condition.


pls anyone help me


Here you go

SELECT students.stud_name
FROM students
WHERE students.part_sport = 'hockey'
INTERSECT
SELECT students.stud_name
FROM students
WHERE student.part_sport = 'football';

Reply With Quote
  #5  
Old April 2nd, 2009, 12:12 AM
ora-cle ora-cle is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Mar 2009
Posts: 2 ora-cle User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 33 m 12 sec
Reputation Power: 0
Query Answer

Quote:
Originally Posted by allasyed
i have one table students

stud_name part_sport

Raju football
kumar cricket
Raju hockey
Balu vollybal
Mahesh hockey
Rajesh cricket

In this i want a record who are the students participating in both football and hockey only.

Result is raju is the only person participatin in football and hockey.

i want oracle query for this condition.


pls anyone help me



SELECT students.stud_name
FROM students
WHERE students.part_sport = 'hockey'
INTERSECT
SELECT students.stud_name
FROM students
WHERE student.part_sport = 'football';

Reply With Quote
  #6  
Old April 2nd, 2009, 07:01 PM
dspence18 dspence18 is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Apr 2009
Location: Colorado
Posts: 2 dspence18 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 21 m 43 sec
Reputation Power: 0
Quote:
Originally Posted by allasyed
i have one table students

stud_name part_sport

Raju football
kumar cricket
Raju hockey
Balu vollybal
Mahesh hockey
Rajesh cricket

In this i want a record who are the students participating in both football and hockey only.

Result is raju is the only person participatin in football and hockey.

i want oracle query for this condition.


pls anyone help me


SELECT DISTINCT stud_name where part_sport in ('football', 'hockey')

or alternatively

SELECT DISTINCT stud_name where part_sport = 'football' or part_sport = 'hockey'

Reply With Quote
Reply

Viewing: Tutorialized ForumsDatabasesOracle > Query


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap