www.webdeveloper.com
Results 1 to 2 of 2

Thread: Dynamic Joins

  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    220

    Dynamic Joins

    Hiya,

    I have this query;

    Code:
    SELECT		COUNT(DISTINCT CA.CASEID) CASECOUNT, N1.FIRSTNAME + ' ' + N1.NAME STAFFNAME, N2.FIRSTNAME + ' ' + N2.NAME SIGNAME
    FROM		CASES CA
    				JOIN CASETYPE CT ON CA.CASETYPE = CT.CASETYPE
    				JOIN CASEEVENT CE ON CA.CASEID = CE.CASEID
    				JOIN CASENAME CN1 ON CA.CASEID = CN1.CASEID AND CN1.NAMETYPE = 'EMP'
    				JOIN NAME N1 ON CN1.NAMENO = N1.NAMENO
    				JOIN CASENAME CN2 ON CA.CASEID = CN2.CASEID AND CN2.NAMETYPE = 'SIG'
    				JOIN NAME N2 ON CN2.NAMENO = N2.NAMENO
    WHERE		CA.CASECATEGORY NOT IN ('X') AND CE.EVENTNO = -16
    				AND CE.EVENTDATE BETWEEN { ts '2011-04-01 00:00:00.000' } AND { ts '2012-03-31 23:59:59.997' }
    				AND N1.NAMENO IN (-93, -9963)
    GROUP BY	N1.FIRSTNAME, N1.NAME, N2.FIRSTNAME, N2.NAME
    ORDER BY	N1.NAME, N1.FIRSTNAME
    which results in thsi output;

    Code:
    518	Records Department	Jon Doe
    1	Records Department	Records Department
    6	Records Department	Joe Bloggs
    1	Records Department	Jill Bloggs
    57	Joe Bloggs		Joe Bloggs
    however the output should be;

    Code:
    518	Records Department	Jon Doe
    1	Records Department	Records Department
    1	Records Department	Jill Bloggs
    63	Joe Bloggs		Joe Bloggs
    So in essence, where the staffname (2nd column) is 'Records Department', the join should be made against the signatoryname (3rd column).

    I've tried in the join and where statements but it doesn't work;

    Code:
    CASE WHEN N1.NAMENO = -93 THEN 'SIG' ELSE 'EMP' END
    Can provide table definitions if needed.

  2. #2
    Join Date
    May 2012
    Posts
    29
    I have added another table, CASES.


    SELECT COUNT(CA.CASEDESC), N.FIRSTNAME + ' ' N.NAME STAFFNAME
    FROM CASES CA JOIN NAMETYPE NT ON CA.NAMENO = NT.NAMENO JOIN
    NAME N ON NT.NAMENO = N.NAMENO

    Would show this;

    2 Joe Bloggs
    1 John Smith
    2 Jon Doe

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles