kfm2011
asked on
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),GETDAT E(),111)= CONVERT(VARCHAR(10),conver t(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)
thanks
select distinct Audit.docid
from dbo.ae_audit as Audit
where CONVERT(VARCHAR(10),GETDAT
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Of course I mean "as" and not "ad" in the WHERE clause:
...
where
cast(getdate() as date)=cast(tsstamp as date)
ASKER
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
thanks
Open in new window