Suppose I have two separate tables that I watch to query. Both of these tables has a relation with a third table. How can I query both tables with a single, non UNION based query? I want the result of the search to rank the results by comparing a field on each table.

Here's a theoretical example. I have a User table. That User can have both CDs and books. I want to find all of that user's books and CDs with a single query matching a string ("awesome" in this example).

A UNION based query might look like this:

SELECT "book" AS model, name, ranking 
 FROM book 
WHERE name LIKE 'Awesome%' 
UNION 
SELECT "cd" AS model, name, ranking 
  FROM cd 
 WHERE name LIKE 'Awesome%' 
ORDER BY ranking DESC

How can I perform a query like this without the UNION? If I do a simple left join from User to Books and CDs, we end up with a total number of results equal to the number of matching cds timse the number of matching books. Is there a GROUP BY or some other way of writing the query to fix this?