I have the following scenario:
2 Tables Patient and Activity
The Patient table contains: DNI, First and Last name. The Activity table contains: Patient's DNI, Activity.
Within the activity table I have repeated the DNI of the patients several times (since they perform several activities).
I need a query where I can bring my DNI without being repeated from the Activity table and the names and surnames of the Patient table.
I tried with count but it gives me errors using it together with group by
It seems that you need a simple JOIN . There are several ways to make the DNI unique, but an alternative is:
SELECT A.DNI,
P.Nombre,
P.Apellido
FROM ( SELECT DISTINCT DNI
FROM Actividad) AS A
INNER JOIN Paciente AS P
ON A.DNI = P.DNI;
Another alternative that I imagine will be faster is to use EXISTS :
SELECT *
FROM Paciente AS P
WHERE EXISTS(SELECT 1 FROM Actividad
WHERE DNI = P.DNI);
You have to use a inner join :
select pa.dni, pa.nombre, pa.apellido, ac.actividad from Paciente pa
inner join Actividad ac on pa.dni = ac.dni
pa is an alias of the Patient table, the same as ac of Activity.