By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,675 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

posted for the 2 time and still no reply, is it hard for you?

P: 4
IT IS THE SECOND TIME I POST THIS PROBLEM, AND STILL NO REPLY, IS IT HARD TO FIND A SOLUTION?

AGAIN:

Table name: registration

Fields:

registration_number....................student_nam e.............course_name
1................................................. .......mike..............................english
2 .................................................. .....lara................................french
3 .................................................. .....peter..............................math
4 .................................................. .....mike..............................math

Query= SELECT student_name FROM registration WHERE course_name="english" AND "math";

the query should give back: "mike", since mike is the only student who is taking "english" and "math" at the same time.

Simply, the query is not working, I know it is not the right way to get what i want, but WHAT IS THE RIGHT WAY?
Nov 25 '06 #1
Share this Question
Share on Google+
7 Replies


nico5038
Expert 2.5K+
P: 3,072
Hmm, strange to have a mike registred twice, but to query that use:

select count(*), student_name from registration where course_name in ("english","math")
group by student_name
having count(*) = 2;

This will count the number of matching courses and only select those having the match with all courses.

Nic;o)
Nov 25 '06 #2

NeoPa
Expert Mod 15k+
P: 31,660
Desperadou,

Nico's solution perfectly answers your problem.

However, please understand that this is a web site where members offer their time and expertise for free.
It is not your RIGHT to expect answers at all. If you get no response (which is very rare in my experience) you are still receiving value for money (It is, after all, free).

Having said that, if you notice it, you are very welcome to bring it to our attention (politely of course).
Nov 25 '06 #3

ADezii
Expert 5K+
P: 8,669
IT IS THE SECOND TIME I POST THIS PROBLEM, AND STILL NO REPLY, IS IT HARD TO FIND A SOLUTION?

AGAIN:

Table name: registration

Fields:

registration_number....................student_nam e.............course_name
1................................................. .......mike..............................english
2 .................................................. .....lara................................french
3 .................................................. .....peter..............................math
4 .................................................. .....mike..............................math

Query= SELECT student_name FROM registration WHERE course_name="english" AND "math";

the query should give back: "mike", since mike is the only student who is taking "english" and "math" at the same time.

Simply, the query is not working, I know it is not the right way to get what i want, but WHAT IS THE RIGHT WAY?
SELECT registration.student_name, Count(registration.student_name) AS Course_Count
FROM registration
WHERE (((registration.course_name)="english" Or (registration.course_name)="math"))
GROUP BY registration.student_name
HAVING (((Count(registration.student_name))>1));
Nov 25 '06 #4

100+
P: 1,646
IT IS THE SECOND TIME I POST THIS PROBLEM, AND STILL NO REPLY, IS IT HARD TO FIND A SOLUTION?

AGAIN:

Table name: registration

Fields:

registration_number....................student_nam e.............course_name
1................................................. .......mike..............................english
2 .................................................. .....lara................................french
3 .................................................. .....peter..............................math
4 .................................................. .....mike..............................math

Query= SELECT student_name FROM registration WHERE course_name="english" AND "math";

the query should give back: "mike", since mike is the only student who is taking "english" and "math" at the same time.

Simply, the query is not working, I know it is not the right way to get what i want, but WHAT IS THE RIGHT WAY?
You almost have it in your query.
First, I imagine that you only want to see Mike once in the query output so we should modify your sql statement this way;
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT student_name 
  2. FROM registration 
  3. WHERE course_name='english' AND 'math'
  4.  
Of course we can't write a where clause like this so we need to change this slightly to get exactly what you want;
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT student_name 
  2. FROM registration 
  3. WHERE course_name='english' 
  4. AND course_name ='math'
  5.  
  6.  
Nov 25 '06 #5

nico5038
Expert 2.5K+
P: 3,072
Sorry willakawill, but:
SELECT student_name
FROM registration
WHERE course_name='english'
AND course_name ='math'

will give no output at all as the field [course_name] will never hold both valuesat the same time....
Only an OR relation could work, but will result in two records.

Nic;o)
Nov 25 '06 #6

NeoPa
Expert Mod 15k+
P: 31,660
ALL answers are, of course, welcomed (and encouraged :) ).
In this thread though, I draw your attention to Nico's answer as it handles every point perfectly.

I considered :embarrassed: a correction myself earlier along the lines suggested by ADezii, but then I looked again and realised that =2 is actually the more correct.
>1 will work, but there can only ever be two (assuming that there will never be a duplicate of all the three fields), because of the WHERE clause.
Nov 25 '06 #7

100+
P: 1,646
Sorry willakawill, but:
SELECT student_name
FROM registration
WHERE course_name='english'
AND course_name ='math'

will give no output at all as the field [course_name] will never hold both valuesat the same time....
Only an OR relation could work, but will result in two records.

Nic;o)
Yup. My bad. Thank you.
Both of the other suggestions work
Nov 26 '06 #8

Post your reply

Sign in to post your reply or Sign up for a free account.