Re: modeling either/or relationship...

jason.glumidge_at_gmail.com wrote:

> David Portas wrote:
> > I think the different tables for 2- and 4-door vehicles was a poorly
> > chosen example - useful only to illustrate how to extend the design.
> > The number of doors is an attribute that all vehicles have in common so
> > it more sensibly belongs in one place only - the Vehicles table.
> >
> > More generally, a union and joins should allow you to interrogate the
> > data across all tables without resorting to procedural code in an
> > external script.
> >
> > --
> > David Portas
>
> Thanks for your response. Simplifying it slightly, if I wanted to
> determine the total number of cars stored, the logical process might
> be:
>
> 1) Obtain the table names for individual car types (SUV, SED, etc) from
> the master table (so I can determine where the cars are stored).
> 2) Iterate through each of these type names counting the no. of rows in
> each of their corresponding sub table.
> 3) Sum these counts.
>
> But how to link these steps together into one statement is still
> eluding me, specifically the link between steps 1 and 2? Much
> appreciated if anyone can illuminate me. Many thanks, Jason.

How about:

SELECT COUNT(*) FROM Vehicles;

Or maybe:

SELECT COUNT(*)
 FROM Vehicles
 WHERE vehicle_type IN
  (... list of types);

-- 
David Portas