Querying multiple subclassed tables
-
Hi all, Here's a situation. I have table A which has several subclassed tables, say A1, A2, and A3. Table A has a primary key named foo which will only exist once in one of the subclassed tables. Now, I want to do a select statement to grab foo from table A and where ever it might be in the subclassed tables. Is it possible to write a single select statement instead of having to write 3 seperate select statements to get to the subclassed data? Thanks, Keith
-
Hi all, Here's a situation. I have table A which has several subclassed tables, say A1, A2, and A3. Table A has a primary key named foo which will only exist once in one of the subclassed tables. Now, I want to do a select statement to grab foo from table A and where ever it might be in the subclassed tables. Is it possible to write a single select statement instead of having to write 3 seperate select statements to get to the subclassed data? Thanks, Keith
You would normally use the following:
select A.*, A1.*, A2.*, A3.* from A left outer join A1 on A1.id = A.id left outer join A2 on A2.id = A.id left outer join A3 on A3.id = A.id where A.id = 'foo'This will only return one row - the outer joins will either return proper field values (for the appropriate sub-class table) or nulls (for the inappropriate ones). Regards Andy
-
You would normally use the following:
select A.*, A1.*, A2.*, A3.* from A left outer join A1 on A1.id = A.id left outer join A2 on A2.id = A.id left outer join A3 on A3.id = A.id where A.id = 'foo'This will only return one row - the outer joins will either return proper field values (for the appropriate sub-class table) or nulls (for the inappropriate ones). Regards Andy
Thanks, Andy. That seems to do the trick. I'm curious though, as you add subclassed tables, would performance degrade significantly? I'm using SQL Server 2000. I'm not too worried about performance right now but it's something that would be good to know. Thanks, Keith
-
Thanks, Andy. That seems to do the trick. I'm curious though, as you add subclassed tables, would performance degrade significantly? I'm using SQL Server 2000. I'm not too worried about performance right now but it's something that would be good to know. Thanks, Keith
As long as each of the tables involved have a unique index or PK constraint (preferably clustered) specified on the key column the query should perform well. I would say that the problem comes with the maintenance effort required to add subclassed tables to all of your queries. I try not to overuse this design for that reason, rather than for performance. An alternative common design that may perform better would be to merge all of the sub-classed tables into the ancestor table. If you are worried about performance and encapsulation then you might consider using a hybrid of the two designs - so that the most commonly-used information is merged into the ancestor table, with the sub-classed tables only required in exceptional cases. Like many design decisions - there is no right or wrong answer:) Andy
-
As long as each of the tables involved have a unique index or PK constraint (preferably clustered) specified on the key column the query should perform well. I would say that the problem comes with the maintenance effort required to add subclassed tables to all of your queries. I try not to overuse this design for that reason, rather than for performance. An alternative common design that may perform better would be to merge all of the sub-classed tables into the ancestor table. If you are worried about performance and encapsulation then you might consider using a hybrid of the two designs - so that the most commonly-used information is merged into the ancestor table, with the sub-classed tables only required in exceptional cases. Like many design decisions - there is no right or wrong answer:) Andy
I totally agree about the maintenance issue that arises from having so many subclassed tables in such a query. I began to explore a different path and ended up not going with huge query that you suggested. I'm trying my "bestest" to do an n-tier approach and that huge query didn't fit right with what I had. Though who knows if I'm doing it close to right. lol Thanks again. Keith