selecting members with not published docs

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 2005 Forums

Other SQL Server Topics (2005)

selecting members with not published docs

Author

Topic

shakthiA
Starting Member

12 Posts

Posted - 2009-03-09 : 06:05:24

Hi

I want to select the members who have not published since their registration.

I was doing the Select like below but it is giving me always the all users with noofpublications when I try to put a condition I could build the sql statement. Could anyone please tell me

SELECT U.Username, M.CreateDate, M.Email,
(SELECT COUNT(S.PUB_ID) FROM
Pub P, SHELF S
WHERE
P.PUB_ID = S.PUB_ID AND
) AS NoOfPubs
FROM
Profile DP INNER JOIN Users U ON U.UserId = DP.UserId
INNER JOIN Membership M ON M.UserId = DP.UserId AND IsApproved = 1
AND DATEDIFF(D,M.CreateDate,GETDATE()) <= 30

Here I tried to put

((SELECT COUNT(S.PUB_ID) FROM
Pub P, SHELF S
WHERE
P.PUB_ID = S.PUB_ID AND
) < 1) AS NoOfPubs

instead of (SELECT COUNT(S.PUB_ID) FROM
Pub P, SHELF S
WHERE
P.PUB_ID = S.PUB_ID AND
) AS NoOfPubs

But it not working.

Can anyone help me.

Thanks in advance,

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-09 : 18:36:43

What field is common between Pub and your main tables?

If you can list table structure of each table; with sample entries it makes it easier to help. Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources