Problem solve Get help with specific problems with your technologies, process and projects.

Combining like tables

I have a database (MS Access XP) where, based on what value is selected from a previous drop down list (school), a combo box will select a different table to displays names. The basic idea is:

If school="abc" then get all records from table "abc", else if school="xyz" then get all records from table "xyz".

Can you help me with a SQL statement to do this? I can use the UNION to get both tables to display, but that's not what I what -- I want only the records in the specific table.

My first inclination is to recommend combining your tables, creating a SCHOOL column. If redesign is not easily accomplished, then you could solve the problem using a UNION set operator and a VIEW. In the case of MS Access, you could use a sub-query.

select * 
  from ( select 'ABC' as School, ABC.*
           from ABC
         UNION ALL
         select 'XYZ' as School, XYZ.*
           from XYZ
  where School = 'XYZ';

For More Information

This was last published in September 2002

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.