SQL returns double data (probably wrong SQL query)

Question

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.

JohnHC · Accepted Answer · 2016-09-30 09:26:41Z

5

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;

answered Sep 30, 2016 at 9:26

JohnHC

11.2k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL returns double data (probably wrong SQL query)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related