dynamic join based on column value?

Hi,

I have three tables.

The "Master table has a recordid, a masterID, a "IsSubField" and other stuff.

I need to do a join to a second table based on the MasterID...

However for each record, if the "IsSubField" has a True value then it has to use table A to JOIN to, where as if it's False, then it uses table B.

Make sense?

Anyone got any pointers?

Thanks in advance

James

Something like this might work (untested):

Code Snippet

SELECT
m.Col1,
m.Col2,
a.Col2,
b.Col2
FROM MasterTable m
JOIN TableA a
ON ( m.MasterID = a.MasterID
AND m.IsSubField = 'TRUE'
)
JOIN TableB b
ON ( m.MasterID = b.MasterID
AND m.IsSubField = 'False'
)

|||

I am not sure that I get this; maybe something like this?

--drop table dbo.masterTable
--drop table dbo.TableA
--drop table dbo.tableB
go

create table dbo.masterTable
( MasterID integer,
Col1 integer,
col2 integer,
isSubField varchar(5)
)
go
insert into dbo.masterTable
select 1, 11, 21, 'True' union all
select 2, 12, 22, 'False' union all
select 3, 13, 23, 'True'
go

create table dbo.TableA
( MasterID integer,
Col2 integer
)
go

insert into tableA
select 1, 101 union all
select 2, 102 union all
select 3, 103
go

create table dbo.TableB
( MasterID integer,
Col2 integer
)
go

insert into tableB
select 1, 201 union all
select 2, 202 union all
select 3, 203
go

SELECT
m.MasterId,
m.Col1 as m_col1,
m.Col2 as m_col2,
b.Col2 as [subfield?]
FROM MasterTable m
JOIN
( select masterID,
'True' as isSubField,
Col2
from TableA
union all
select masterId,
'False' as isSubField,
Col2
from TableB
) b
on m.MasterId = b.masterId
and m.isSubfield = b.isSubfield

/*
MasterId m_col1 m_col2 subfield?
-- -- -- --
1 11 21 101
2 12 22 202
3 13 23 103
*/

|||

Hi,

thanks for that - I haven't tested it yet, but the snytax is working.. so just need to get a few test records in now..

|||

Hi,

I have this as my query - but don't get any results:

SELECT m.MaterialID, m.MasterID, a.ProductName, b.MaterialName, m.IsSubMaterial
FROM redrose1.Materials_Listings m INNER JOIN
dbo.Products a ON m.MasterID = a.ProductID AND (m.IsSubMaterial = 'No' OR
m.IsSubMaterial IS NULL) INNER JOIN
redrose1.Materials b ON m.MasterID = b.MaterialID AND m.IsSubMaterial = 'Yes'
WHERE (m.MaterialID = @.MaterialID)

any ideas?

|||

Both JOINs should be LEFT JOIN.

Code Snippet

SET NOCOUNT ON

DECLARE @.MasterTable table
( MasterID integer,
Col1 integer,
col2 integer,
isSubField varchar(5)
)

INSERT INTO @.MasterTable VALUES ( 1, 11, 21, 'True' )
INSERT INTO @.MasterTable VALUES ( 2, 12, 22, 'False' )
INSERT INTO @.MasterTable VALUES ( 3, 13, 23, 'True' )

DECLARE @.TableA table
( MasterID integer,
Col2 integer
)

INSERT INTO @.TableA VALUES ( 1, 101 )
INSERT INTO @.TableA VALUES ( 2, 102 )
INSERT INTO @.TableA VALUES ( 3, 103 )

DECLARE @.TableB table
( MasterID integer,
Col2 integer
)

INSERT INTO @.TableB VALUES ( 1, 201 )
INSERT INTO @.TableB VALUES ( 2, 202 )
INSERT INTO @.TableB VALUES ( 3, 203 )

SELECT
m.Col1,
m.Col2,
a.Col2,
b.Col2
FROM @.MasterTable m
LEFT JOIN @.TableA a
ON ( m.MasterID = a.MasterID
AND m.IsSubField = 'TRUE'
)
LEFT JOIN @.TableB b
ON ( m.MasterID = b.MasterID
AND m.IsSubField = 'False'
)

Col1 Col2 Col2 Col2
-- -- -- --
11 21 101 NULL
12 22 NULL 202
13 23 103 NULL

|||excellent - thanks!!