Question: I have an sql query that is almost right but when a user has more than 1 aircraft in the database they end up with multiple row listings in the results returned from:

```
SELECT  a.this_pay, 
    u.username, 
    u.user_id, 
    c.type_id,
    c.is_primary, 
    x.typename, 
    p.pf_city, 
    p.pf_state, 
    n.username AS non_username, 
    n.user_id AS non_user_id, 
    n.pf_city AS non_pf_city, 
    n.pf_state AS non_pf_state 
    FROM (((((jowner_event_attendees a 
    LEFT JOIN jowner_users u ON a.user_id = u.user_id) 
    LEFT JOIN jowner_aircraft c ON a.user_id = c.user_id) 
    LEFT JOIN jowner_aircraft_types x ON x.id = c.type_id) 
    LEFT JOIN jowner_profile_fields_data p ON a.user_id = p.user_id) 
    LEFT JOIN jowner_not_users n ON a.user_id = n.user_id) 
    WHERE a.event_id = 28 ORDER BY COALESCE(u.username,non_username);

```

I end up with a list that has multiple entries for anyone with multiple aircraft, I only want 1 entry returned no matter how many aircraft entries they have in the 'jowner\_aircraft' table.

I also would prefer to not change this query drastically as other parts of the app rely on it, obviously it needs the revision I am asking for, but please try to answer it without a complete rewrite.

Answer: 
```
SELECT  a.this_pay, 
    u.username, 
    u.user_id, 
    c.type_id, 
    x.typename, 
    p.pf_city, 
    p.pf_state, 
    n.username AS non_username, 
    n.user_id AS non_user_id, 
    n.pf_city AS non_pf_city, 
    n.pf_state AS non_pf_state 
    FROM (((((jowner_event_attendees a 
    LEFT JOIN jowner_users u ON a.user_id = u.user_id) 
    LEFT JOIN jowner_aircraft c ON a.user_id = c.user_id AND c.is_primary=1) 
    LEFT JOIN jowner_aircraft_types x ON x.id = c.type_id) 
    LEFT JOIN jowner_profile_fields_data p ON a.user_id = p.user_id) 
    LEFT JOIN jowner_not_users n ON a.user_id = n.user_id) 
    WHERE a.event_id = 28 ORDER BY COALESCE(u.username,non_username);

```
You should just need to add another where clause to eliminate all but the record where is\_primary is true.

```
SELECT  a.this_pay, 
u.username, 
u.user_id, 
c.type_id,
c.is_primary, 
x.typename, 
p.pf_city, 
p.pf_state, 
n.username AS non_username, 
n.user_id AS non_user_id, 
n.pf_city AS non_pf_city, 
n.pf_state AS non_pf_state 
FROM jowner_event_attendees a 
LEFT JOIN jowner_users u ON a.user_id = u.user_id
LEFT JOIN jowner_aircraft c ON a.user_id = c.user_id 
LEFT JOIN jowner_aircraft_types x ON x.id = c.type_id
LEFT JOIN jowner_profile_fields_data p ON a.user_id = p.user_id
LEFT JOIN jowner_not_users n ON a.user_id = n.user_id 
WHERE a.event_id = 28 
AND c.is_primary = 1
ORDER BY COALESCE(u.username,non_username);

```

Substituting whatever the correct value for "true" is for is\_primary.