Ivaro18 Ivaro18 - 2 months ago 10
MySQL Question

SQL returns double data (probably wrong SQL query)

problem && context

I'm getting double results. Table Registration contains 2 columns & 2 rows,

event_id
,
participant_id
having
1,3
and
1,1
.

Now when I select all registrations (
select * from registration
) this does work.

The names
(firstname, lastname)
of the 2 participants are
('test' ,'test1')
and
('Gregor', 'unknown')
.

desired results

I want to make a searchfunctionality that will return just the participants that registered them for an event with the 'test' in their first or lastname.

current results

With the query below I get 4 results (all the same participant).

select * from participant p, event e, registration r
where p.firstname LIKE '%test%'
OR p.lastname LIKE '%test%'
AND p.id = r.participant_id
AND e.id = r.event_id
AND e.id = 1;


error messages

none, just returns 4 rows instead of 1.

Answer

Unwrapped OR, and a distinct will help... Try

select distinct * 
from participant p, event e, registration r 
where (p.firstname LIKE '%test%'
  OR p.lastname LIKE '%test%' )
AND p.id = r.participant_id 
AND e.id = r.event_id 
AND e.id = 1;
Comments