Question on SQL experission for complicated query

[cy163]

Hello All,

I have a problem on how to retrieve records from a database using SQL query. It is a little bit complicated query. I do not know what SQL query experission is correct.


In the database table, there are four fields "ClassID", "Personal Nmae", "Age", "Score". The following is an example for illustration.

Class Name Age Score
0 Bob 20 78 <====
0 John 26 66 <****
2 Wilson 28 88
1 John 26 77 <****
3 Alice 25 56
1 Bob 20 89 <====


In the above example table, I would like to know the scores of students who share the same name, at the same age, BUT in different class (ClassID is specified by the user). Actually, I need a SQL SELECT experission which will lead to a query result like the following


Bob 78 89
John 66 77

[hensa22]

try this statement.
O hope it's useful.


select table_aux.name,max(table_aux.score)
from (
select name,score from test as t
where exists(select 1 from test as x where t.name=x.name and t.class<>x.class ) ) as table_aux
group by r.name

[cjard]

Hello All,

I have a problem on how to retrieve records from a database using SQL query. It is a little bit complicated query. I do not know what SQL query experission is correct.


In the database table, there are four fields "ClassID", "Personal Nmae", "Age", "Score". The following is an example for illustration.

Class Name Age Score
0 Bob 20 78 <====
0 John 26 66 <****
2 Wilson 28 88
1 John 26 77 <****
3 Alice 25 56
1 Bob 20 89 <====


In the above example table, I would like to know the scores of students who share the same name, at the same age, BUT in different class (ClassID is specified by the user). Actually, I need a SQL SELECT experission which will lead to a query result like the following


Bob 78 89
John 66 77



SELECT
name,
age,
SUM(CASE WHEN class = 0 THEN score ELSE 0 END) as col1,
SUM(CASE WHEN class = 1 THEN score ELSE 0 END) as col2
FROM
table
INNER JOIN
(
SELECT
name, age
FROM
table
GROUP BY
name, age
HAVING
count(*) = 2
) a
ON
a.name = table.name AND
a.age = table.age
GROUP BY
name, age


I didnt want to generate the result table without the age, because if you have 2 people called bob of different ages, your result set cannot be used to distinct them.. they would all beome rolled into one