How can I perform this query between related tables without using UNION?
- by jeremy
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?
