Q

Joining a table to one of two other tables

My database has several entities and for each entity, several specialized sub entitities.

So for example, I may have a general entity called "Address" and then 2 sub entities called "AddressInt" and "AddressUSA". All addresses have records in the Address table and in addition, there is location specific information for each address. This location specific information is stored in it's own separate table (AddressUSA = for the US, AddressInt = for International).

Similarly, I have a "User" table that holds information common to all users and then specialized tables such as "GradUser", "UndergradUser" etc., that hold addtional specific information about that particular kind of user.

Taking a concrete example, say, I have a "AddressType" table that contains:

Table: AddressType 
type   value
1      USA
2      Int

One address record then looks like:

Table: Address 
Column      Value
id          123
Street      1 Main Street
City        some city
addtype     1 

Table: AddressUSA id 123 State PA Zip 12345

So based on the addtype ('1' in the above example), I have to get the remaining information from "AddressUSA". If the AddressType had been '2', I would then have to get the remaining information from the "AddressInt" table instead. Note, I can't do a simple join between "Address" and "AddressUSA" (using "123"), because I don't know the name of the table ("AddressUSA" or "AddressInt") until I have read the "addtype" field in the "Address" table.

So what I really need to do is:

  • Read a record from Address

  • Read its corresponding type

  • Based on the type, choose another table and read additional information.

I am using MySQL, which does not have sub-queries but does allow me to select into temporary tables easily.

What's the most efficient SQL to read an entire address (common + specific information). I want to minimize traffic between my client and the server so I would ideally speaking, like to do this all on the database side itself.


With the tables as you have defined them, the query would be

select Address.type, Street, City, State, Zip, 'USA'
  from Address inner join AddressUSA
    on Address.id = AddressUSA.id
 where Address.type='1'
union all
select Address.type, Street, City, Province, PostalCode, Country
  from Address inner join AddressInt
    on Address.id = AddressInt.id
 where Address.type='2'

The way this works is that the Address row will only match up with a row from one table or the other, not both. UNION ALL prevents the unnecessary sort for duplicate rows (there can't be any). Note that UNION is implemented in MySQL 4.0.0.

The above approach works only insofar as you can "shoe-horn" the different columns from the two specialty tables into the same columnar format in the result set. In other words, State and Province share the same column, as do Zip and PostalCode, and 'USA' and Country -- even though for each address, you only get one or the other type returned. The first column tells which one you got, and it's up to you to handle the contents of the columns properly, e.g. by labelling them on the output according to the format. This is the chore inherent in carrying separate specialized subentitities and yet wanting to get either one back in a query without knowing in advance which type it is (if you knew ahead of time, you wouldn't need the two parts of the UNION, you'd only run the one you needed).

Another method would be to have a three-way outer table join.

select Address.type, Street, City, State, Zip, Province, PostalCode, Country
  from Address 
 left outer join AddressUSA
    on Address.id = AddressUSA.id
 left outer join AddressIntA
    on Address.id = AddressInt.id

Caution: I am not sure the above is valid in MySQL.

This double outer join would also return only one row per address, but instead of the USA or International values occupying common columns, this time either the USA or International columns will have values, and the other columns will contain nulls. Again, it's up to you to handle this properly once you receive the result set.

Note that this second query is simplified -- very much simplified -- if instead of separate AddressUSA and AddressInt tables, these subentities were "side-by-side" (so to speak) in the same table. It wouldn't even have to be separate from the main Address table, either. You could put both the USA and the International columns into the Address table, and thus have one table instead of two or three. No joins necessary! The "type" column would still be required, as it was in the second query above, and in fact the result set from the second query is exactly what you'd get from a simpler single table -- on every row, some of the columns would have nulls.

For More Information


This was first published in January 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close