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
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
- What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL gurus are waiting to answer your toughest questions.
This was first published in January 2002