Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query

Author

Topic

KabirPatel
Yak Posting Veteran

54 Posts

Posted - 2008-01-11 : 06:53:05

Hi,

I have some tables as follows:

TableA

TableAID Title
--------------------------------
T1 Test 1
T2 Test 2
T3 Test 3

TableB

TableBID Title
--------------------------------
G1 Bad1
G2 Bad2
G3 Bad3

TableATableB (this is a one to one relationship between TableA and TableB)

TableAID TableBID
--------------------------------
T1 G2
T2 G1
T3 G3

TableARoles (this is a one to many relationship between TableA records and "Roles")

TableAID Role
--------------------------------
T1 Role1
T1 Role2
T2 Role1
T2 Role3

TableBRoles (this is a one to many relationship between TableB records and "Roles")

TableBID Role
--------------------------------
G2 Role1
G2 Role2
G1 Role1
G1 Role4

I am having trouble writing a query to produce the following output:

TableBID TableAID Desc
---------------------------------------------
G2 T1 Full Role Match
G1 T2 Partial Role Match

in other words the query should list the matches between TableA and TableB and should also specify whether the Roles mapped to the TableA record match the Roles mapped to the TableB record.

e.g. T1 above has the Roles "Role1" and "Role2" and so does G2. As such they have a "full match".

Thanks in advance
Kabir

nr
SQLTeam MVY

12543 Posts

Posted - 2008-01-11 : 07:35:28

select t.TableAID, t.TableBID,
case when max(case when arole+brole is null then 1 else 0 end) = 0 then 'full' else 'partial' end
from
TableATableB t
left join
(select TableATableB.TableAID, TableATableB.TableBID, arole = TableARoles.Role
from TableATableB join TableARoles on TableARoles.TableAID = TableATableB.TableAID) ta
on t.TableAID = ta.TableAID
and t.TableBID = ta.TableBID
left join
(select TableATableB.TableAID, TableATableB.TableBID, brole = TableBRoles.Role
from TableATableB join TableBRoles on TableBRoles.TableBID = TableATableB.TableBID) tb
on t.TableAID = tb.TableAID
and t.TableBID = tb.TableBID
and ta.arole = tb.brole
group by t.TableAID, t.TableBID
having max(coalesce(arole, brole)) = max(coalesce(arole, brole))

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources