How to write a SELECT statement when I don't know in advance what columns I want?

My ap开发者_如何学运维plication has several tables: a master OBJECT table, and several tables for storing specific kinds of objects: CAT, SHOE and BOOK.

Here's an idea of what the table columns look like:

object

object_id    (primary key)
object_type  (string)

cat

cat_id       (primary key)
object_id    (foreign key)
name         (string)
color        (string)

shoe

shoe_id      (primary key)
object_id    (foreign key)
model        (string)
size         (string)

book

book_id      (primary key)
object_id    (foreign key)
title        (string)
author       (string)

From the user's point of view, each specific object is primarily identified by its name, which is a different column for each table. For the CAT table it's name, for the SHOE table it's model, and for the BOOK table it's title.

Let's say I'm handed an object_id without knowing in advance what kind of object it represents -- a cat, a shoe or a book. How do I write a SELECT statement to get this information?

Obviously it would look a little like this:

SELECT object_type,name FROM object WHERE object_id = 12345;

But how do I get the right contents in the "name" column?

It seems like you're describing a scenario where the user's view on the data (objects have names, I don't care what type they are) is different from the model you're using to store the data.

If that is the case, and assuming you have some control over the database objects, I'd probably create a VIEW, allowing you to coalesce similar data for each type of object.

Example on SQL Server:

CREATE VIEW object_names AS
SELECT object_id, name FROM cat
UNION ALL
SELECT object_id, model AS name FROM shoe
UNION ALL
SELECT object_id, title AS name FROM book
GO

You can then SELECT name FROM object_names WHERE object_id = 12345, without concerning yourself with the underlying column names.

Your only real solutions basically boil down to the same thing: writing explicit statements for each specific table and unioning them into a single result set.

You can either do this in a view (giving you a dynamic database object that you can query) or as part of the query (whether it's straight SQL or a stored procedure). You don't mention which database you're using, but the basic query is something like this:

select object_id, name from cat where object_id = 12345 union all
select object_id, model from shoe where object_id = 12345 union all
select object_id, title from book where object_id = 12345 

For SQL Server, the syntax for creating the view would be:

create view object_view as
select 'cat' as type, object_id, name from cat union all
select 'shoe', object_id, model from shoe union all
select 'book', object_id, title from book

And you could query like:

select type, name from object_view where object_id = 12345

However, what you have is a basic table inheritance pattern, but it's implemented improperly since:

• The primary key of child tables (cat, shoe, book) should also be a foreign key to the parent table (object). You should not have a different key for this, unless two cat records can represent the same object (in which case this is not inheritance at all)
• Common elements, such as a name, should be represented at the highest level of the hierarchy as appropriate (in this case in object, since all of the objects have the concept of a "name").

do a join

http://www.tizag.com/sqlTutorial/sqljoin.php

You can't. Why not name them the same thing, or pull that name back to the OBJECT table. You can very easily create a column called Name and put that in the OBEJCT table. This would still be normalized.

It seems like you have a few options. You could use a config file or a 'schema' table. You could rename your tables so that the name of ye column is always te same. You could have the class in your code know its table. You could make your architecture a little less generic, and allow the data access layer to understand the data it's accessing.

Which to choose? What problem are you solving? What problem were you solving, whose solution created this problem?

There's really no way to do this without first SELECTing to find out the kind, then SELECTing a second time to get the actual data. If you only have a few different kinds of objects, you could do it with a single SELECT and a bunch of LEFT JOINs to join all the tables at once, but that doesn't scale well if you've got lots of joiner tables.

But just thinking outside the box a bit, does the "identifier" that users see have to correspond exactly to the primary key in the table? Could you encode the "kind" of the object in the identifier itself? So for example, if object_id 12345 is a shoe you could "encode" this as "S12345" from the user's point of view. A book would be "B4567" and a cat "C2578". Then in your code, just separate out the first letter and use that to decide which table to join on, and the remaining numbers are your primary key.

If you cannot alter the original table due to dependencies, you could probably create a view of the table with uniform column name. More information on how to create views can be found here.

There a table you can look at that tells you the properties of all the tables (and column properties) in your db.

In postgres this is something like pg_stat_alltables, I think there is something similar in sql server. You could query this and work out what you required, then construct a query based on that info...

EDIT: Sorry re-reading the question, I don't think that is what you require. - I've solved a similar problem before by having a surrogate key table - one table with all the id's in and a type id, then a serial/identity column that contains the primary key for that table - this is the id you should use... then you can create a view which looks up the other information based on the type id in that table. The 'entity ref' table would have columns 'entityref' (PK), 'id', 'type id' etc... (that is assuming you can't restructure to use inheritance)