select - MySQL joining data from 2 tables based on foreign key in a third, without duplicates or group by -

i have 3 tables this:

people: +------------+-------------+------+-----+-------------------+----------------+ | field      | type        | null | key | default           |          | +------------+-------------+------+-----+-------------------+----------------+ | id         | int(11)     | no   | pri | null              | auto_increment | | fname      | varchar(32) | no   |     | null              |                | | lname      | varchar(32) | no   |     | null              |                | | dob        | date        | no   |     | 0000-00-00        |                | | license_no | varchar(24) | no   |     | null              |                | | date_added | timestamp   | no   |     | current_timestamp |                | | status     | varchar(8)  | no   |     | allow             |                | +------------+-------------+------+-----+-------------------+----------------+  units: +----------+-------------+------+-----+---------+----------------+ | field    | type        | null | key | default |          | +----------+-------------+------+-----+---------+----------------+ | id       | int(11)     | no   | pri | null    | auto_increment | | number   | varchar(3)  | no   |     | null    |                | | resident | int(11)     | no   | mul | null    |                | | type     | varchar(16) | no   |     | null    |                | +----------+-------------+------+-----+---------+----------------+  visits: +----------+-----------+------+-----+---------------------+----------------+ | field    | type      | null | key | default             |          | +----------+-----------+------+-----+---------------------+----------------+ | id       | int(11)   | no   | pri | null                | auto_increment | | vis_id   | int(11)   | no   | mul | null                |                | | unit     | int(11)   | no   | mul | null                |                | | time_in  | timestamp | no   |     | current_timestamp   |                | | time_out | timestamp | no   |     | 0000-00-00 00:00:00 |                | +----------+-----------+------+-----+---------------------+----------------+ 

there multiple foreign keys linking these tables:

units.resident -> people.id  visits.unit    -> units.id  visits.vis_id  -> people.id 

i able run query find residents ie - people referenced units.resident foreign key:

select  concat(p.lname, ', ', p.fname) 'resident', p.dob 'birthday',  u.number 'unit #'  people p, units u p.id = u.resident order u.number 

it returns results want... however, it'd useful opposite of find people are not residents ie- people aren't referenced units.resident foreign key.

i've tried many different queries, notably inner , left joins, i'm getting waaaaay many duplicate entries (from i've read here, normal). thing i've found works using group license_no, because of "residents" don't have information, this:

select  p.id, concat(p.lname, ', ', p.fname) 'visitor', p.license_no 'license', u.number people p left join units u on u.number <> p.id group p.license_no order p.id; 

this works one resident, who's u.number displayed on results. residents have license_no entries, , can't have 1 odd entry in returned results time, query won't work long-term solution.

how can structure query without group by return results want?

this should work

select   p.id  , p.fname  , p.lname  people p left join  units u on  p.id = u.resident   u.resident null 

extra hint.

table people should called person. u.resident mean person. should person_id there in unit table...

better logic helps write sql better, if name convention clear use.