MS SQL Server 2008 Query Question

My sql is rusty and I need some help.  I have a table ae_audit which has a records that can join to a number of different tables (ae_dl1, ae_dl2, etc.).  ae_audit has a unique docid that can be in one of the other tables(dl1, dl2, etc.) and those table have a record for each page in that document.  So for each record in ae_audit, i'm trying to figure out how many pages there are by joining to a union in a sub select.  I've tried various queries and can't figure it out.  The different subqueries work fine, i cant bring them together.
thanks

select distinct Audit.docid
from dbo.ae_audit as Audit
where CONVERT(VARCHAR(10),GETDATE(),111)= CONVERT(VARCHAR(10),convert(datetime,tsstamp),111)
 AND
audit.docid = ((select docid, pagenum from dbo.ae_dl1 UNION ALL
select docid, pagenum from dbo.ae_dl2 UNION ALL
select docid, pagenum from dbo.ae_dl3 UNION ALL
select docid, pagenum from dbo.ae_dl4 UNION ALL
select docid, pagenum from dbo.ae_dl5 UNION ALL
select docid, pagenum from dbo.ae_dl6 UNION ALL
select docid, pagenum from dbo.ae_dl7 UNION ALL
select docid, pagenum from dbo.ae_dl8 UNION ALL
select docid, pagenum from dbo.ae_dl9) As All_Pages)
kfm2011Asked:
Who is Participating?
 
tim_csConnect With a Mentor Commented:

SELECT
   Audit.DocID
   ,Count(All_Pages.Pagenum) TotalPages
FROM
   dbo.ae_audit Audit
   INNER JOIN (select docid, pagenum from dbo.ae_dl1 UNION ALL
      select docid, pagenum from dbo.ae_dl2 UNION ALL
      select docid, pagenum from dbo.ae_dl3 UNION ALL
      select docid, pagenum from dbo.ae_dl4 UNION ALL
      select docid, pagenum from dbo.ae_dl5 UNION ALL
      select docid, pagenum from dbo.ae_dl6 UNION ALL
      select docid, pagenum from dbo.ae_dl7 UNION ALL
      select docid, pagenum from dbo.ae_dl8 UNION ALL
      select docid, pagenum from dbo.ae_dl9) All_Pages
         ON Audit.DocID = All_Pages.DocID
WHERE
   CONVERT(VARCHAR(10),GETDATE(),111)= CONVERT(VARCHAR(10),convert(datetime,tsstamp),111)
GROUP BY
   Audit.DocID

Open in new window

0
 
ZberteocCommented:
I think the solution below is better then using a UNION. Also i fyou are using the SAL2008 then casting to DATE type is much simpler than using convert:
SELECT
	Audit.DocID
	,Count(*) TotalPages
FROM
	dbo.ae_audit Audit
	LEFT JOIN dbo.ae_dl1 d1
		on d1.DocID=Audit.DocID
	LEFT JOIN dbo.ae_dl2 d2
		on d2.DocID=Audit.DocID
	LEFT JOIN dbo.ae_dl3 d3
		on d3.DocID=Audit.DocID
	LEFT JOIN dbo.ae_dl4 d4
		on d4.DocID=Audit.DocID
	LEFT JOIN dbo.ae_dl5 d5
		on d5.DocID=Audit.DocID
	LEFT JOIN dbo.ae_dl6 d6
		on d6.DocID=Audit.DocID
	LEFT JOIN dbo.ae_dl7 d7
		on d7.DocID=Audit.DocID
	LEFT JOIN dbo.ae_dl8 d8
		on d8.DocID=Audit.DocID
	LEFT JOIN dbo.ae_dl9 d9
		on d9.DocID=Audit.DocID
where
	cast(getdate() ad date)=cast(tsstamp as date)
group by
	Audit.DocID

Open in new window

0
 
ZberteocCommented:
Of course I mean "as" and not "ad" in the WHERE clause:
...
where
	cast(getdate() as date)=cast(tsstamp as date)

Open in new window

0
 
kfm2011Author Commented:
Thanks, you definitely answered my initial question.  As a followup question, the ae_audit may have multiple records (all with the same date for each DocID) and I only want to join one of these to ae_dl1, etc., not all of them.  Any more guidance would be appreciated.

thanks
0
All Courses

From novice to tech pro — start learning today.