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

[desperadou]

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?

[nico5038]

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)

[NeoPa]

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).

[ADezii]

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));

[willakawill]

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;

SELECT DISTINCT student_name 
FROM registration 
WHERE course_name='english' AND 'math'
 

Of course we can't write a where clause like this so we need to change this slightly to get exactly what you want;

SELECT DISTINCT student_name 
FROM registration 
WHERE course_name='english' 
AND course_name ='math'
 
 

[nico5038]

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)

[NeoPa]

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.

[willakawill]

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