How do I add two count(*) results together on two different tables?

Question

I have two tables: Toys and Games.

+--------------------+------------------+ | Field              | Type             | +--------------------+------------------+ | toy_id             | int(10) unsigned | | little_kid_id      | int(10) unsigned | +--------------------+------------------+  +--------------------+------------------+ | Field              | Type             | +--------------------+------------------+ | game_id            | int(10) unsigned | | little_kid1        | int(10) unsigned | | little_kid2        | int(10) unsigned | | little_kid3        | int(10) unsigned | +--------------------+------------------+ 

A little kid can have multiple toys. A little kid can be participating in multiple games at once.

I want a query that will give me the total number of toys + games that a little_kid is involved with.

Basically, I want the sum of these two queries:

 SELECT COUNT(*) FROM Toys WHERE little_kid_id = 900; SELECT COUNT(*) from Games WHERE little_kid1 = 900                                OR little_kid2 = 900                                OR little_kid3 = 900; 

Is it possible to get this in a single SQL query? Obviously, I can sum them programmatically, but that's less desirable.

(I realize that the contrived example makes the schema look ineffecient. Let's assume that we can't change the schema.)

Answer 1

Wrap them up and use subqueries:

SELECT (SELECT COUNT(*) FROM Toys WHERE little_kid_id = 900)+ (SELECT COUNT(*) from Games WHERE little_kid1 = 900                                OR little_kid2 = 900                                OR little_kid3 = 900) AS SumCount 

Voila!