How can I perform this query between related tables without using UNION?

Question

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?

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?

(EDIT: The reason I would like to avoid the Union approach is because this is actually a DQL query and Doctrine does not support UNION. If there's no way to do this without UNION, I'll go the native SQL route. In addition, the real query contains a bunch of additional columns that do not map as nicely against one another in the above example.)

Answer 1

If you are trying to avoid union one way is to create view.

EDIT: To create view you have two options

In case your query is only for selecting records there should be no problem with

CREATE VIEW media AS
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

If you need view that can be updated then it might fly if you refactor:

• create table that will hold all the data AND media type
• create two views that will split data on media type (since these views are simple 1:1 queries to underlaying tables they should be updatable and you should be able to use them in ORM mapping or other SQL queries)

EDIT2: I forgot to comment on the fact that UNION ALL is a must over UNION unless you want MySQL to start building index on the disk every time you run the view/query (thanks HLGEM).

Answer 2

Unless there is a compelling reason to not use a UNION, then just use the UNION.

Answer 3

Maybe you could try something like this... the example assumes the third table is called User:

$q = Doctrine_Query::create()
  ->select('c.cd, b.book')
  ->from('User u')
  ->LeftJoin('Cd c ON u.user_id = c.user_id AND c.name LIKE ?, 'Awesome%')
  ->LeftJoin('Book b ON u.user_id = b.user_id AND b.name LIKE ?, 'Awesome%');
$result = $q->execute();

Answer 4

Think of how you'd model this in an OO app. You'd create a superclass that you extend for books and CD's, and your user would then own a set of Collectibles. Any given object in that set is either a book or a CD (or other type of collectible) but it has exactly one of these subtypes.

You can do something similar with SQL, by creating a table corresponding to the supertype:

CREATE TABLE Collectibles (
  collectible_id SERIAL PRIMARY KEY,
  user_id        INT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

Then each subtype contains a reference to make it collectible:

CREATE TABLE Books (
  book_id   BIGINT UNSIGNED PRIMARY KEY
  book_name VARCHAR(100) NOT NULL,
  FOREIGN KEY (book_id) REFERENCES Collectibles(collectible_id)
);

CREATE TABLE CDs (
  cd_id   BIGINT UNSIGNED PRIMARY KEY
  cd_name VARCHAR(100) NOT NULL,
  FOREIGN KEY (cd_id) REFERENCES Collectibles(collectible_id)
);

Now you can do your query and be assured you won't get a Cartesian product:

SELECT u.*, COALESCE(b.book_name, d.cd_name) AS media_name
FROM Users u
JOIN Collectibles c ON (u.user_id = c.user_id)
LEFT OUTER JOIN Books b ON (b.book_id = c.collectible_id)
LEFT OUTER JOIN CDs d ON (d.cd_id = c.collectible_id);