Need help with this query/sub-query

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

SQL Server Development (2000)

Need help with this query/sub-query

Author

Topic

imcoolanytime
Starting Member

2 Posts

Posted - 2006-02-02 : 15:15:47

SELECT DISTINCT
A.TRANSACTION_ID AS 'TRANS_ID' ,
(LIST.NAME + ', ' +LIST.SHORTNAME) AS 'NAME'

FROM WITS_MAIN A (NOLOCK)
INNER JOIN RLVNTDATA R(NOLOCK) ON R.VALUE = A.TRANSACTION_ID AND R.RLVNTDATANAME = 'TRANSACTION_NUMBER'
INNER JOIN PROCS P (NOLOCK) ON R.PROCID = P.PROCID AND P.[NAME] <> 'Request for Job Code / Position Number'
AND P.STATE IN ('R', 'V')
LEFT JOIN ( SELECT W.PROCID, W.PRTCP, M.[NAME], M.SHORTNAME
FROM WITEM W (NOLOCK)
INNER JOIN MEMBER M (NOLOCK) ON W.PRTCP = M.MEMBERID
WHERE W.STATE IN ('I', 'R', 'V', 'P')
) LIST ON R.PROCID = LIST.PROCID AND LIST.SHORTNAME IS NOT NULL
LEFT JOIN WITS_ORGS B (NOLOCK) ON B.ADMIN_CODE = A.ADMIN_CODE
LEFT JOIN WITS_SPA_PROCESSING D (NOLOCK) ON D.TRANSACTION_ID = A.TRANSACTION_ID
LEFT JOIN WITS_RECRUITMENT_SELECTION E (NOLOCK) ON E.TRANSACTION_ID = A.TRANSACTION_ID
LEFT JOIN WITS_NEW_POSITION F (NOLOCK) ON F.TRANSACTION_ID = A.TRANSACTION_ID
WHERE A.DATE_SF52_RECEIVED BETWEEN '1/27/2005' AND '2/1/2006'
AND A.ACTION_TYPE IN ('CLASSIFICATION WITH RECRUITMENT /SELECTION' , 'RECRUITMENT /SELECTION')
AND A.STATUS = 'ACTIVE'
ORDER BY A.TRANSACTION_ID

In the above query I need to get transactions from wits_main (a) table even thought they (LIST.NAME + ', ' +LIST.SHORTNAME) is null....
can anyone throw in suggestions?
Thanks in advance.
IMC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-02 : 15:32:53

I think you need LEFT OUTER JOIN here:

INNER JOIN MEMBER M (NOLOCK) ON W.PRTCP = M.MEMBERID

Tara Kizer
aka tduggan