Joins - 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)

Joins

Author

Topic

KabirPatel
Yak Posting Veteran

54 Posts

Posted - 2008-01-24 : 13:23:47

Hi,

I have some tables as follows:

TableA

JobID | RoleNo
-----------------------
Job001 Role001
Job001 Role002
Job001 Role003
Job002 Role002
Job003 Role005

TableB

CountryCodePackageID | CountryCode | PackageID | Duration
-------------------------------------------------------------
1 GL Package001 5
2 GL Package002 10
3 UK Package001 7

TableC

RoleNo | CountryCodePackageID
-------------------------------
Role001 1
Role001 2

For TableC, the only CountryCodePackageID's that are valid are those that belong to 'GL'.

I need to write a proc that takes the CountryCode as a paramter and lists all the Jobs mapped to their Roles mapped to their Packages where the Package exists for the CountryCode passed.

In other words for the above data if the paramter UK was passed I would like to see :

JobID RoleNo PackageID Duration
--------------------------------------------
Job001 Role001 Package001 7
Job002 Role002 NULL NULL
Job003 Role005 NULL NULL

In the first row, Role001 is mapped to Package001. Also, Package001 exists for the UK. Therefore it is output.

For row two, Role002 is mapped to Package002. However, this Package does not exist for the UK. Hence a NULL value is displayed.

For row 3 no PackageID exists for Role005.

I have tried usnig left joins but to no avail. Could somebody please enlighten me?

Cheers,
Kabir

Ifor
Aged Yak Warrior

700 Posts

Posted - 2008-01-25 : 06:23:07

This may help, although it will also show NULL rows for Role002 and Role003 of Job001.

SELECT A.JobID, A.RoleNo, B2.PackageID, B2.Duration
FROM TableC C
    JOIN TableB B1
        ON C.CountryCodePackageID = B1.CountryCodePackageID
    JOIN TableB B2
        ON B1.PackageID = B2.PackageID
            AND B2.CountryCode = 'UK'
    RIGHT JOIN TableA A
        ON C.RoleNo = A.RoleNo

If you want to use a left join then the inner joins will need to be nested.

SELECT A.JobID, A.RoleNo, B2.PackageID, B2.Duration
FROM TableA A
    LEFT JOIN
    (
        TableC C
        JOIN TableB B1
            ON C.CountryCodePackageID = B1.CountryCodePackageID
        JOIN TableB B2
            ON B1.PackageID = B2.PackageID
                AND B2.CountryCode = 'UK'
    )
        ON A.RoleNo = C.RoleNo

To get the results exactly as you have shown try:

SELECT A2.JobID, A2.RoleNo, B2.PackageID, B2.Duration
FROM TableC C
    JOIN TableB B1
        ON C.CountryCodePackageID = B1.CountryCodePackageID
    JOIN TableB B2
        ON B1.PackageID = B2.PackageID
            AND B2.CountryCode = 'UK'
    JOIN TableA A1
        ON C.RoleNo = A1.RoleNo
    RIGHT JOIN TableA A2
        ON A1.JobID = A2.JobID
WHERE A2.RoleNo = C.RoleNo
    OR C.RoleNo IS NULL

KabirPatel
Yak Posting Veteran

54 Posts

Posted - 2008-01-29 : 07:16:59

Thanks - that worked a treat.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources