sql query union join

Angela4eva
Angela4eva used Ask the Experts™
on

Employee
EmployeeID Name userid
1           aa    adev
2           bb    afev


Empolyee-documents
EmployeeID  DocumentID
1            1
1            2
2            3




Documents
Docid  Status date

1      abc   12/16/2009
2      CCH   12/22/2009
3      CCD   12/21/2009
 
DocumentsHistory
DochistoryID   Docid   Status   date
   1             1      PPD     12/1/2008
   2             1      GGB     12/13/2008
   3             2      NAF     12/2/2008
   4             2      PPD     12/3/2008
   5             2      Xyz     12/7/2008
   6             3      AAD     12/7/2008



I want to write query that takes parameters as begin and end date and
I want to get all the records from document history or documents table
 that had  status of PPD atleat once but not the status as XYZ

The query should return

For above example it will return
EmployeeiD Name userid docid status date
1           aa    adev  1    PPD       12/1/2008


this will get the record docid=2 because it had a status of XYZ in one of the records
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try this.

Greg



;WITH StatusWanted
AS 
(
	SELECT DocHistoryID
	FROM DocumentsHistory AS a
	WHERE a.Status = 'PPD' 
),
StatusNotWanted
AS 
(
	SELECT DocID
	FROM DocumentsHistory AS b
	WHERE b.Status = 'XYZ' 
)
 
SELECT e.EmployeeID, e.[Name], e.UserID, dh.DocID, dh.Status, dh.Date
FROM Employee AS e INNER JOIN	
		[Employee-Documents] AS ed ON e.EmployeeID = ed.EmplyeeID INNER JOIN 
		DocumentsHistory AS dh ON dh.DocID = ed.DocumentID INNER JOIN
		StatusWanted AS sw ON sw.DocHistoryID = dh.DochistoryID
WHERE dh.DocID NOT IN(SELECT DocID FROM StatusNotWanted)

Open in new window

Commented:

SELECT *,E.EmployeeID,E.[Name],E.userid
      FROM Employee E Inner Join Empolyee_documents Ed On E.EmployeeID = Ed.EmpolyeeID
      inner join Documents D On D.Docid = Ed.documentid
      inner join DocumentsHistory DH On DH.Docid = D.Docid
WHERE
      DH.Status = 'PPD'
      And Dh.Date = '2008-12-01'
SharathData Engineer

Commented:
Are you looking for this?

>> I want to write query that takes parameters as begin and end date
Can you explain your logic here?
select E.EmployeeId,E.Name,E.UserId,DH.DocId,DH.Status,DH.Date
  from DocumentHistory DH
  join Empolyee_documents ED on DH.DocId = ED.DocumentId
  join Employee E on ED.EmployeeId = E.EmployeeId
 where DH.Status = 'PPD' and DH.DocId not in (select DocId from DocumentHistory where Status = 'XYZ')

Open in new window

Muhammad KashifDevelopment Manager
Commented:
try this query


DECLARE @startdate DATE,
		@endDate DATE
SET @startdate = '2008/01/01' 
SET @endDate = '2009/10/05'  
;WITH CTE AS
(
	Select Docid , Status , date From Documents
	union all
	Select Docid , Status , date From DocumentsHistory
)
SELECT E.EmployeeID, E.Name, E.UserId, A.* FROM CTE A 
INNER JOIN Empolyee_documents ED ON ED.DocumentID = A.Docid
INNER JOIN Employee E ON E.EmployeeID = ED.EmployeeID
WHERE A.date between @startdate and @endDate AND A.Status = 'PPD' AND A.Docid NOT IN  
(SELECT DISTINCT Docid FROM CTE WHERE Status = 'XYZ')

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial