Working with subquery - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Working with subquery

Author

Topic

subhaoviya
Posting Yak Master

135 Posts

Posted - 2011-01-11 : 03:29:18

I need to select both FILENAMES and [Date]. if i update the [Date] column in outside select statement along with FILENAMES i am getting error. please help me get the correct query.

SELECT count(DISTINCT FILENAMEFK) as FILENAMES FROM table2 b
WHERE EXISTS
(SELECT
CONVERT(VARCHAR(10),a.[DateTimeStamp],111) AS [Date],
COUNT(DISTINCT a.[SourceFileDescriptorFK]) AS [FilesProcessed]
FROM table1 a)
WHERE a.[SourceFileDescriptorFK]=b.[SourceFileDescriptorPK]
GROUP BY
CONVERT(VARCHAR(10),a.[DateTimeStamp],111))

thanks in advance
by
subha

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-11 : 03:55:27

Can you post the error message you are getting?

By the way there are two Where clauses for one Select query.. highlighted one is the second one and is incorrect Syntax

SELECT count(DISTINCT FILENAMEFK) as FILENAMES FROM table2 b
WHERE EXISTS
(
SELECT
CONVERT(VARCHAR(10),a.[DateTimeStamp],111) AS [Date],
COUNT(DISTINCT a.[SourceFileDescriptorFK]) AS [FilesProcessed]
FROM table1 a
)

WHERE a.[SourceFileDescriptorFK]=b.[SourceFileDescriptorPK]
GROUP BY
CONVERT(VARCHAR(10),a.[DateTimeStamp],111))

subhaoviya
Posting Yak Master

135 Posts

Posted - 2011-01-11 : 04:55:51

Hi mik,

thanks for your reply, i have modified the query using joins.
now it is working fine.

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources