Hi
I have a table KDOCUMENT with the columns
[DocID],
[Title],
[CreatedByUser],
[CreateDate],
[LastModifiedByU serName],
[LastModificatio nDate],
[Comment]
On this table I want to find the test documents so that they can be deleted, hence I have created the query to find documents with the words "test" and "delete" in [Title] and [Comment] looking like this:
SELECT [DocID], [Title],[CreatedByUser], [CreateDate],[LastModifiedByU serName], [LastModificatio nDate], [Comment]
FROM [DMSDB].[dbo].[KDOCUMENT]
WHERE [Title] LIKE '%test%'
OR [Comment] LIKE '%test%'
OR [Title] LIKE '%delete%'
OR [Comment] LIKE '%delete%'
OR [OnlineVersionsS ize] = 0.
This one is working fine returning 5070 rows with documents containing test and delete.
Problem is that we only want to list the documents belonging to users that have X documents or more (for instance 20), because we have to contact the people and ask them which documents actually are test documents. If someone only have 1 document we don't want to bother them, it is more important to clean up the "real heavy" user's documents.
So, I created a query
SELECT COUNT([CreatedByUser]) AS 'Number of docs', [CreatedByUser]
FROM [DMSDB].[dbo].[KDOCUMENT]
WHERE [Title] LIKE '%test%'
OR [Comment] LIKE '%test%'
OR [Title] LIKE '%delete%'
OR [Comment] LIKE '%delete%'
OR [OnlineVersionsS ize] = 0
GROUP BY [CreatedByUser]
HAVING COUNT([CreatedByUser]) > 20
ORDER BY COUNT([CreatedByUser]) DESC
returning correctly the number of documents for each user and the user name. It also limits itself to listing only users with a count of 20 or more.
Now, I want to combine these two, listing all documents with DocID and everything where a user has 20 documents or more. But when I do this:
SELECT [DocID], [Title],[CreatedByUser], [CreateDate], [LastModifiedByU serName], [LastModificatio nDate], [Comment]
FROM [DMSDB].[dbo].[KDOCUMENT]
WHERE [Title] LIKE '%delete%'
OR [Comment] LIKE '%test%'
OR [Title] LIKE '%test%'
OR [Comment] LIKE '%delete%'
OR [OnlineVersionsS ize] = 0
AND [CreatedByUser] IN (
SELECT [CreatedByUser]
FROM [DMSDB].[dbo].[KDOCUMENT]
GROUP BY [CreatedByUser]
HAVING COUNT([CreatedByUser]) > 20)
ORDER BY [CreatedByUser]
I lists all the documents as in the first query, returning 5070 rows while it should return about 4715 (I know this by adding up the numbers in the count query).
It has been a long time since I've been working with SQL, and I am struggling a bit here. Can someone help me? I get the same result using EXISTS, and when using = I of course get the error that it is returning more than one row.
3 2505 ck9663 2,878
Recognized Expert Specialist
Hi
I have a table KDOCUMENT with the columns
[DocID],
[Title],
[CreatedByUser],
[CreateDate],
[LastModifiedByU serName],
[LastModificatio nDate],
[Comment]
On this table I want to find the test documents so that they can be deleted, hence I have created the query to find documents with the words "test" and "delete" in [Title] and [Comment] looking like this:
SELECT [DocID], [Title],[CreatedByUser], [CreateDate],[LastModifiedByU serName], [LastModificatio nDate], [Comment]
FROM [DMSDB].[dbo].[KDOCUMENT]
WHERE [Title] LIKE '%test%'
OR [Comment] LIKE '%test%'
OR [Title] LIKE '%delete%'
OR [Comment] LIKE '%delete%'
OR [OnlineVersionsS ize] = 0.
This one is working fine returning 5070 rows with documents containing test and delete.
Problem is that we only want to list the documents belonging to users that have X documents or more (for instance 20), because we have to contact the people and ask them which documents actually are test documents. If someone only have 1 document we don't want to bother them, it is more important to clean up the "real heavy" user's documents.
So, I created a query
SELECT COUNT([CreatedByUser]) AS 'Number of docs', [CreatedByUser]
FROM [DMSDB].[dbo].[KDOCUMENT]
WHERE [Title] LIKE '%test%'
OR [Comment] LIKE '%test%'
OR [Title] LIKE '%delete%'
OR [Comment] LIKE '%delete%'
OR [OnlineVersionsS ize] = 0
GROUP BY [CreatedByUser]
HAVING COUNT([CreatedByUser]) > 20
ORDER BY COUNT([CreatedByUser]) DESC
returning correctly the number of documents for each user and the user name. It also limits itself to listing only users with a count of 20 or more.
Now, I want to combine these two, listing all documents with DocID and everything where a user has 20 documents or more. But when I do this:
SELECT [DocID], [Title],[CreatedByUser], [CreateDate], [LastModifiedByU serName], [LastModificatio nDate], [Comment]
FROM [DMSDB].[dbo].[KDOCUMENT]
WHERE [Title] LIKE '%delete%'
OR [Comment] LIKE '%test%'
OR [Title] LIKE '%test%'
OR [Comment] LIKE '%delete%'
OR [OnlineVersionsS ize] = 0
AND [CreatedByUser] IN (
SELECT [CreatedByUser]
FROM [DMSDB].[dbo].[KDOCUMENT]
GROUP BY [CreatedByUser]
HAVING COUNT([CreatedByUser]) > 20)
ORDER BY [CreatedByUser]
I lists all the documents as in the first query, returning 5070 rows while it should return about 4715 (I know this by adding up the numbers in the count query).
It has been a long time since I've been working with SQL, and I am struggling a bit here. Can someone help me? I get the same result using EXISTS, and when using = I of course get the error that it is returning more than one row.
Use parenthesis to group your conditions -
-
WHERE ([Title] LIKE '%delete%'
-
OR [Comment] LIKE '%test%'
-
OR [Title] LIKE '%test%'
-
OR [Comment] LIKE '%delete%'
-
OR [OnlineVersionsSize] = 0)
-
AND [CreatedByUser] IN (
-
SELECT [CreatedByUser]
-
FROM [DMSDB].[dbo].[KDOCUMENT]
-
GROUP BY [CreatedByUser]
-
HAVING COUNT([CreatedByUser]) > 20)
-
-
Conditions are evaluated left to right. If one of those ORs is true, your whole condition is true.
Similar to math formula:
2 + 3 * 5 = 25
2 + (3 * 5) = 17
Happy coding!
-- CK
Use parenthesis to group your conditions -
-
WHERE ([Title] LIKE '%delete%'
-
OR [Comment] LIKE '%test%'
-
OR [Title] LIKE '%test%'
-
OR [Comment] LIKE '%delete%'
-
OR [OnlineVersionsSize] = 0)
-
AND [CreatedByUser] IN (
-
SELECT [CreatedByUser]
-
FROM [DMSDB].[dbo].[KDOCUMENT]
-
GROUP BY [CreatedByUser]
-
HAVING COUNT([CreatedByUser]) > 20)
-
-
Conditions are evaluated left to right. If one of those ORs is true, your whole condition is true.
Similar to math formula:
2 + 3 * 5 = 25
2 + (3 * 5) = 17
Happy coding!
-- CK
Wow, thank you!
It works like a charm, and the best part is that I was not completely off, it was just some paranthesis missing.
I now just discovered one thing you might be able to help me with:
This query: SELECT [CreatedByUser]
FROM [DMSDB].[dbo].[KDOCUMENT]
GROUP BY [CreatedByUser]
HAVING COUNT([CreatedByUser]) > 20)
counts ALL users in the entire table, but I only want it to count the ones with the criteria listed in the first query (Title and Comment equals "test" and/or "delete". When I try to put:
WHERE ([Title] LIKE '%delete%'
OR [Comment] LIKE '%test%'
OR [Title] LIKE '%test%'
OR [Comment] LIKE '%delete%'
OR [OnlineVersionsS ize] = 0
into the second query like this:
SELECT [DocID], [Title],[CreatedByUser], [CreateDate], [LastModifiedByU serName], [LastModificatio nDate], [Comment], [OnlineVersionsS ize]
FROM [DMSDB].[dbo].[KDOCUMENT]
WHERE [CreatedByUser] IN (
SELECT [CreatedByUser]
FROM [DMSDB].[dbo].[KDOCUMENT]
WHERE ([Title] LIKE '%delete%'
OR [Comment] LIKE '%test%'
OR [Title] LIKE '%test%'
OR [Comment] LIKE '%delete%'
OR [OnlineVersionsS ize] = 0)
GROUP BY [CreatedByUser]
HAVING COUNT([CreatedByUser]) > 20)
it shows 0 results. Any ideas?
There might be another way to do this, so I am very open for suggestions :)
ck9663 2,878
Recognized Expert Specialist
Do you have NULL in CreatedByUser ?
try : -
-
SELECT DocID, Title,CreatedByUser, CreateDate, LastModifiedByUserName,
-
LastModificationDate, Comment, OnlineVersionsSize
-
FROM DMSDB.dbo.KDOCUMENT
-
WHERE CreatedByUser IN
-
(
-
SELECT CreatedByUser
-
FROM DMSDB.dbo.KDOCUMENT
-
WHERE (Title LIKE '%delete%' OR Comment LIKE '%test%' OR Title LIKE '%test%' OR Comment LIKE '%delete%' OR OnlineVersionsSize = 0)
-
and CreatedByUser IS NOT NULL
-
GROUP BY CreatedByUser
-
HAVING COUNT(*) > 20
-
)
-
WHERE CreatedByUser IS NOT NULL
-
-
You should be careful with IN. Read more about it here
-- CK
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Haisoo Shin |
last post by:
Hello..
I am working on a PHP/PEAR web page that shows statistics of how many
people read a certain article during given period of time.
I have, say, two tables called 'books' and 'logs'. The 'books' table
has fields named 'bookNo', 'bookName', and so on, and 'logs' table has
fields named 'bookNo', and 'time'. Whenever somebody accesses a book,
I insert a row to the 'logs' table with 'INSERT INTO logs VALUES ($no,
NOW());'
|
by: Nachi |
last post by:
I am getting 2 resultsets depending on conditon, In the second
conditon i am getting the above error could anyone help me..........
CREATE proc sp_count_AllNewsPapers
@CustomerId int
as
declare @NewsId int
set @NewsId = (select NewsDelId from NewsDelivery where
CustomerId=@CustomerId )
|
by: Greg Stark |
last post by:
I find I often want to be able to do joins against views where the view are
aggregates on a column that has an index. Ie, something like
SELECT a.*, v.n
FROM a
JOIN (select a_id,count(*) as n group by a_id) as v USING (a_id)
where there's an index on b.a_id. assume there are other tables being joined
so I can't just move the aggregate to the outermost layer.
|
by: Greg Stark |
last post by:
Postgresql 7.4b2 (approximately, compiled out of CVS)
When I have a subquery that has a complex subquery as one of the result
columns, and then that result column is used multiple times in the parent
query, the subquery is inlined for each one. This means multiple redundant
executions of the subquery.
I recall there was a way to defeat this optimization involving introducing an
extra subquery layer somewhere. But I'm failing to be able...
|
by: Kenny G |
last post by:
Below is a query that I currently have. I need to produce a subquery
so that the top five of the CodeCount is returned. I appreciate your
help.
SELECT .PX_SURGEON, .PX_CODE,
Count(.PX_CODE) AS CodeCount, .PX_TITLE
FROM INNER JOIN ON .PX_CODE = .PX_CODE
GROUP BY .PX_SURGEON, .PX_CODE,
.PX_TITLE
ORDER BY .PX_SURGEON, Count(.PX_CODE)
| |
by: Edwin Pauli |
last post by:
Hi,
Yesterday i had upgrade my PostgreSQL server from version 7.2.4
to 7.4.1.
There are troubles with a subquery after the upgrade.
Here is the query:
SELECT team_naam, team_id, wpim, (
|
by: laurenq uantrell |
last post by:
I'm trying to return an integer from the following table that returns
the number of unique cities:
tblEmployees
Name City
John Boston
Frank New York
Jim Omaha
Betty New York
|
by: JT |
last post by:
I'm using access 2000 and with assistance thought I had this problem
licked.
This query works:
SELECT Q3.id, Q3.date, Max(Q3.Rank) AS Ninetieth
FROM query3 AS Q3
WHERE (((Q3.Rank)<=(0.9*(SELECT COUNT() FROM query3 as Q3A WHERE
(Q3A.ID = Q3.ID) AND (Q3A. = Q3.)))))
GROUP BY Q3.id, Q3.date;
|
by: Aleck |
last post by:
Hie.
I have a trigger that monitors changes to my table fields but I get an error saying subquery returned more than one value.Below is the code for my trigger, hope you will figure out whats happening..
CREATE TRIGGER trgmyTableAuditFields ON myTable
WITH ENCRYPTION
FOR INSERT, UPDATE, DELETE
AS
BEGIN
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |