I would need some help of you with a SQL-query.

I would like to get uniqe emailadresses from a database.

My problem is that I get several copys of the mailadress for a uniqe student. I make sub queries with select max and set distinct and so on, but I don't get it right.

Here is a simple description of the tables.


Students
ID_Student (int)
Email (text)
Firstname (text)
Lastname (text)
Ezine (boolean)


Cards
ID_Student (int)
ID_Studentunion (int)
Cardtype (int)
Cardnumber (text)
Expiredate (date)


Studentunions
ID_Studentunion (int)
Schoolname (text)

A. ID_Student is uniqe
B. A student can have several of cards.
C. The relation between the tables Students and Cards is ID_Student
D. The relation between the tables Cards and Studentunions is ID_Studentunion

Criterias: Student.Ezine = 1, Cards.Cardtype = 1
Order by: Cards.Expiredate Desc

SELECT DISTINCT ?

/micke

Ja, men problemet är att en student kan ha fler kort i tabellen cards. När man sedan relaterar till den andra tabellen Studentunions så blir emailadressen ej unik.

Ge exempel på det resultat du får nu och det resultat du vill ha.

<CODE>
SELECT Email, COUNT(Expiredate) CardsCount, MIN(Expiredate) MinExpireDate
FROM Students s, Cards C
WHERE S.Ezine = 1
AND
C.CardType = 1
AND
S.ID_Student = C.ID_Student
GROUP BY Email
ORDER BY 3 DESC
</CODE>

Komplettera själv till det resultat du vill ha.
/Pelle

SQL:
select
s.id_student
, max(s.email) as email
, max(c.cardtype) as cardtype
, max(c.cardnumber) as cardnumber
, max(c.expiredate) as expiredate
, max(u.schoolname) as schoolname
from s_students s
, cards c
, studentunions u
where s.id_student = c.id_student
and c.id_studentunion = u.id_studentunion
and s.ezine = 1
and c.cardtype = 1
group by s.id_student

Resultset:
---------------------------
Student1 with max email, max cardtype and number, max school
Student2 with max email, max cardtype and number, max school
Student3 with max email, max cardtype and number, max school

The query returns unique email for students, but the resultset relevance equals zero in this case.