Better way to create MySQL view?

Here's the scenario:
9 different tables, 'ca1' through 'ca9', each with unique index named 'ca_id' which relates them.
Each of the 9 has a field 'ca_last_update' reflecting the date the last change to that record was made.

View which contains one record per ca_id and the oldest value of ca_last_update from among its 9 records.  If any of the 9 dates for a given ID are NULL, need NULL.

Tried creating view with sub-queries but, of course, MySQL doesn't allow that.  No go.

Ended up creating 9 separate views (one for each table), another view that uses UNIONs to combine them into one large record set (resulting in 9 x the number of unique IDs - a really big number), and still another to get the MIN() of the date column for each ID from that view.

I feel like I'm making this too complicated.  There's got to be a better, faster way...  Anybody know what that is?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve BinkCommented:
The fact you have nine tables with the same information is something worth looking at design-wise.  In the meantime, try something like this:
CREATE VIEW v1 as SELECT ca_id, ca_last_update UNION ALL SELECT ca_id, ca_last_update FROM ca2 UNION ALL etc.. etc..;

CREATE VIEW real_v1 AS SELECT ca_id, MIN(a.ca_last_update FROM v1 GROUP BY ca_id;

Open in new window

You want to find the oldest value of ca_last_update across nine tables - your initial domain is all records.  You'll have to use the UNION of those tables as a source.  Then it is a simple matter of grouping by ca_id and getting the oldest date.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
>>" If any of the 9 dates for a given ID are NULL, need NULL."

how do you determine "missing"?
please demonstate using sample data
springthorpeSoftwareAuthor Commented:
Thanks for the reply!  
Structure was inherited from Access app.  Access has (had?) a 255 limit on fields/record.  There's about 600 involved here, so they grouped the fields into use-related tables.  Each of 9 can be edited independently by different users, so we have to track last update by which user.  Plus there's the developers' no-no regarding wide tables.

Your solution of combining the UNION ALLs into a single query [Duh! Why didn't I think of that!] cut 30% from the query time.
In order to get the "NULL has priority" to work, I ended up with 5 views:

1: Get list of all ca_id's. (ca_last_updates_list)
2: Get list of ca_id's with NULL in any of the 9 date fields (ca_last_updates_null)
3: Get list of all valid (not NULL) dates (your UNION ALL query with IS NOT NULL tests)
4: Get MINs of data in #3. (ca_last_updates_mins)
5: Using #1 as base with LEFT JOINs to #2 and #4, get either NULL (as priority) or oldest date:

CREATE OR REPLACE VIEW `ca_oldest_updates` AS
SELECT `ca_last_updates_list`.`ca_id`,
IF (`ca_last_updates_null`.`ca_id` IS NULL, `ca_last_updates_mins`.`ca_oldest_update`, NULL) AS `ca_last_update`
FROM `ca_last_updates_list`
LEFT JOIN `ca_last_updates_null` ON `ca_last_updates_list`.`ca_id` = `ca_last_updates_null`.`ca_id`
LEFT JOIN `ca_last_updates_mins` ON `ca_last_updates_list`.`ca_id` = `ca_last_updates_mins`.`ca_id`;

Adding this last one increased the result time somewhat, but it's still well below anything that can be noticed by a web user.

If you see a hole, let me know.
Thanks again,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.