Joining two tables multiple times

Hi
I have two tables (in an third party application, I cannot change the
data structure) as follows:-
T1 - Main data
Amount Ref1 Ref2 Ref3
==============================
100 A A A
150 A B A
200 A B B
T2 - Reference data
Type Value Name
=========================
Ref1 A Area 1
Ref1 B Area 2
...
Ref2 A Dept 1
Ref2 B Dept 2
...
Ref3 A Type 1
Ref3 B Type 3
At a simple level I want to be able to return (though obviously there
are many more complex applications of this data that I want to do):-
Amount Name1 Name2 Name3
==================================
100 Area 1 Dept 1 Type 1
150 Area 1 Dept 2 Type 1
200 Area 1 Dept 2 Type 2
At the moment I achive this by creating a query for each Refn type
Select * FROM T2
WHERE Type = 'Refn'
and then joining T1 several types to each of these queries. Is there a
way of creating this without creating the queries first?
App is SQL server, I'm using Access 2000 to query, but quite happy (and
permitted) to use passthrough queries instead.
Any help gratefully received!
MattTry this
create table #T1(Amount int, Ref1 char(1),Ref2 char(1),Ref3 char(1))
insert into #T1(Amount,Ref1,Ref2,Ref3) values(100,'A','A','A')
insert into #T1(Amount,Ref1,Ref2,Ref3) values(150,'A','B','A')
insert into #T1(Amount,Ref1,Ref2,Ref3) values(200,'A','B','B')
create table #T2(Type char(4),Value char(1), Name varchar(10))
insert into #T2(Type,Value,Name) values ('Ref1','A','Area 1')
insert into #T2(Type,Value,Name) values ('Ref1','B','Area 2')
insert into #T2(Type,Value,Name) values ('Ref2','A','Dept 1')
insert into #T2(Type,Value,Name) values ('Ref2','B','Dept 2')
insert into #T2(Type,Value,Name) values ('Ref3','A','Type 1')
insert into #T2(Type,Value,Name) values ('Ref3','B','Type 2')
select T1.Amount, t2a.Name as Name1, t2b.Name as Name2, t2c.Name as
Name3
from #T1 as T1
inner join #T2 as t2a on t2a.Type='Ref1' and T1.Ref1=t2a.Value
inner join #T2 as t2b on t2b.Type='Ref2' and T1.Ref2=t2b.Value
inner join #T2 as t2c on t2c.Type='Ref3' and T1.Ref3=t2c.Value
drop table #T1
drop table #T2|||markc600@.hotmail.com wrote:
> Try this
> [snipped]
> select T1.Amount, t2a.Name as Name1, t2b.Name as Name2, t2c.Name as
> Name3
> from #T1 as T1
> inner join #T2 as t2a on t2a.Type='Ref1' and T1.Ref1=t2a.Value
> inner join #T2 as t2b on t2b.Type='Ref2' and T1.Ref2=t2b.Value
> inner join #T2 as t2c on t2c.Type='Ref3' and T1.Ref3=t2c.Value
> drop table #T1
> drop table #T2
Mark
Many thanks for such a swift reply - just what I needed. Turned the
real world SQL into this (which worked a treat).
Matt
SELECT T1.CODE, T1.NAME AS COSTCENTRE, T2.NAME AS FUNCTION, T3.NAME AS
REGION, T4.NAME AS LNHREGION, T8.NAME AS BRANCH
FROM SADFLDGRIP AS DATA
INNER JOIN
SSRFACC AS CA ON
CA.SUN_DB = 'RIP'
AND
DATA.ACCNT_CODE = CA.ACCNT_CODE
INNER JOIN
SSRFANV AS T1 ON T1.CATEGORY = 'T1'
AND
T1.SUN_DB = 'RIP'
AND
DATA.ANAL_T1 = T1.CODE
INNER JOIN
SSRFANV AS T2 ON T2.CATEGORY = 'T2'
AND
T2.SUN_DB = 'RIP'
AND
DATA.ANAL_T2 = T2.CODE
INNER JOIN
SSRFANV AS T3 ON T3.CATEGORY = 'T3'
AND
T3.SUN_DB = 'RIP'
AND
DATA.ANAL_T3 = T3.CODE
INNER JOIN
SSRFANV AS T4 ON T4.CATEGORY = 'T4'
AND
T4.SUN_DB = 'RIP'
AND
DATA.ANAL_T4 = T4.CODE
INNER JOIN
SSRFANV AS T8 ON T8.CATEGORY = 'T8'
AND
T8.SUN_DB = 'RIP'
AND
DATA.ANAL_T8 = T8.CODE
WHERE DATA.PERIOD >= 2004001 AND DATA.PERIOD <=2005001 AND
CA.ACCNT_TYPE = 'P'
GROUP BY T1.CODE, T1.NAME, T2.NAME, T3.NAME, T4.NAME, T8.NAME
;|||you can try this 3 solutions
(i let myself redefine and rename some of your tables and columns):
SET NOCOUNT ON;
SET ANSI_NULLS ON;
USE YOUR_DB;
IF EXISTS(SELECT * FROM YOUR_DB.INFORMATION_SCHEMA.TABLES
WHERE table_name='MainData') DROP TABLE MainData;
IF EXISTS(SELECT * FROM YOUR_DB.INFORMATION_SCHEMA.TABLES
WHERE table_name='RefData') DROP TABLE RefData;
CREATE TABLE MainData(
amnt INTEGER NOT NULL,
ref1 CHAR(1) NOT NULL CHECK(ref1 IN('A', 'B')),
ref2 CHAR(1) NOT NULL CHECK(ref2 IN('A', 'B')),
ref3 CHAR(1) NOT NULL CHECK(ref3 IN('A', 'B')));
CREATE INDEX MainData_ref1_Idx ON MainData(ref1);
CREATE INDEX MainData_ref2_Idx ON MainData(ref2);
CREATE INDEX MainData_ref3_Idx ON MainData(ref3);
INSERT INTO MainData
SELECT 100, 'A', 'A', 'A' UNION ALL
SELECT 150, 'A', 'B', 'A' UNION ALL
SELECT 200, 'A', 'B', 'B';
CREATE TABLE RefData(
ref_tp CHAR(4) NOT NULL,
ref_vl CHAR(1) NOT NULL CHECK(ref_vl IN('A', 'B')),
ref_nm CHAR(6) NOT NULL);
CREATE INDEX RefData_ref_tp_Idx ON RefData(ref_tp);
INSERT INTO RefData
SELECT 'Ref1','A','Area 1' UNION ALL
SELECT 'Ref1','B','Area 2' UNION ALL
SELECT 'Ref2','A','Dept 1' UNION ALL
SELECT 'Ref2','B','Dept 2' UNION ALL
SELECT 'Ref3','A','Type 1' UNION ALL
SELECT 'Ref3','B','Type 2';
-- amnt ref_nm1 ref_nm2 ref_nm3
-- 100 Area 1 Dept 1 Type 1
-- 150 Area 1 Dept 2 Type 1
-- 200 Area 1 Dept 2 Type 2
SELECT amnt,
(SELECT R1.ref_nm FROM RefData AS R1
WHERE R1.ref_tp = 'Ref1' AND R1.ref_vl = M.ref1) AS ref_nm1,
(SELECT R2.ref_nm FROM RefData AS R2
WHERE R2.ref_tp = 'Ref2' AND R2.ref_vl = M.ref2) AS ref_nm2,
(SELECT R3.ref_nm FROM RefData AS R3
WHERE R3.ref_tp = 'Ref3' AND R3.ref_vl = M.ref3) AS ref_nm3
FROM MainData AS M
SELECT M.amnt,
R1.ref_nm AS ref_nm1, R2.ref_nm AS ref_nm2, R3.ref_nm AS ref_nm3
FROM MainData AS M, RefData AS R1, RefData AS R2, RefData AS R3
WHERE R1.ref_tp = 'Ref1' AND R1.ref_vl = M.ref1
AND R2.ref_tp = 'Ref2' AND R2.ref_vl = M.ref2
AND R3.ref_tp = 'Ref3' AND R3.ref_vl = M.ref3
SELECT M.amnt,
R1.ref_nm AS ref_nm1, R2.ref_nm AS ref_nm2, R3.ref_nm AS ref_nm3
FROM RefData AS R3
RIGHT OUTER JOIN RefData AS R2
RIGHT OUTER JOIN RefData AS R1
RIGHT OUTER JOIN MainData AS M
ON R1.ref_vl = M.ref1 AND R1.ref_tp = 'Ref1'
ON R2.ref_vl = M.ref2 AND R2.ref_tp = 'Ref2'
ON R3.ref_vl = M.ref3 AND R3.ref_tp = 'Ref3'
-- WHERE R1.ref_tp = 'Ref1'
-- AND R2.ref_tp = 'Ref2'
-- AND R3.ref_tp = 'Ref3'