Sunday, February 19, 2012

Efficiently joining same table twice

My main table has the following structure:

t1 (id_primary, id_secundary, name) i.e. [(1,1,"name1"), (2,1,"name2")]

I want to join this table with the following second table:

t2 (id_primary, id_secundary, value) i.e. [(1, NULL, "value1"),
(NULL,1,"value2")]

The join should first try to find a match on id_primary and only if that
fails it should find a match on id_secundary. Every row in t1 is matched
against a single row in t2.

The following query works:

select
a.name, isnull(b.value, c.value)
from
t1 a left outer join t2 b on a.id_primary = b.id_primary
left outer join t2 c on a.id_secundary = c.id_secundary

I'm wondering though if it would be possible to write a query that only uses
t2 once, since it actualy is quite a complex query that is calculated twice
now. Any ideas (besides using a temp table)?Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.|||On 26 Feb 2005 06:56:56 -0800, "--CELKO--" <jcelko212@.earthlink.net>
wrote:

>Please post DDL, so that people do not have to guess what the keys,
>constraints, Declarative Referential Integrity, datatypes, etc. in your
>schema are. Sample data is also a good idea, along with clear
>specifications.

CREATE TABLE t1 (
[id_primary] [int] NOT NULL ,
[id_secundary] [int] NOT NULL ,
[name] [char] (10) NOT NULL
)
GO

CREATE TABLE t2 (
[id_primary] [int] NULL ,
[id_secundary] [int] NULL ,
[value] [char] (10) NOT NULL
)
GO

INSERT INTO t1 VALUES (1,3,'Name1')
GO
INSERT INTO t1 VALUES (2,3,'Name2')
GO
INSERT INTO t2 VALUES (1,NULL,'Value1')
GO
INSERT INTO t2 VALUES (NULL,3,'Value2')
GO

The result of the join should be the following:

name
---- ----
Name1 Value1
Name2 Value2

The first row in t1 ('Name1') will find a match on the id_primary
column (id_primary=1) in t2.
The second row in t1 ('Name2') will not find a match on id_primary (2)
in t2, but will find a match on id_secundary (3) in t2|||honda (hondass50@.hotmail.com) writes:
> I'm wondering though if it would be possible to write a query that only
> uses t2 once, since it actualy is quite a complex query that is
> calculated twice now. Any ideas (besides using a temp table)?

I was trying to achieve something, but I could not get it to work. In
any case, it is far from certain that it would have been more effecient
that your current query, which appears to be best way to write it anyway.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

No comments:

Post a Comment