How to create a query that shows additional information from ISA relationships

I have a database of Star Wars characters, and three tables:

Characters(cname, alias, gender, pname)

Droid(cname, dtype, mainskill)

JediSith(cname, level)

where Droid and JediSith have an IS-A relationship with Characters (non-covering, no overlap), and cname for both Droid and JediSith is a foreign key that references Characters.

How would I create a query that would show all the information from Characters, as well as the additional information from JediSith or Droid (where applicable), for a given cname?

Currently I have three queries, one for each table, that shows the relevant information from each table. Is it possible to make this into one query?

Solution

Use LEFT JOIN to join the other two tables to Characters:

SELECT c.cname, c.alias, c.gender, c.pname, d.dtype, d.mainskill, j.level
FROM Characters c
LEFT JOIN Droid d ON d.cname = c.cname
LEFT JOIN JediSith j ON j.cname = c.cname

If you're running the query in MS Access, you need to use parentheses:

SELECT c.cname, c.alias, c.gender, c.pname, d.dtype, d.mainskill, j.level
FROM (Characters c
      LEFT JOIN Droid d ON d.cname = c.cname)
      LEFT JOIN JediSith j ON j.cname = c.cname