January 31, 2012 at 6:05 pm
Hi,
I have one table student, i.e stdnt_id,stdnt_name,stdnt_activity
i have values in student table like this.
stdnt_id stdnt_name stdnt_activity
1 kiran 5
1 kiran 10
1 kiran 15
2 sachin 5
2 sachin 10
3 venkat 5
3 venkat 10
3 venkat 15
4 kumar 5
4 kumar 10
5 naveen 5
5 naveen 10
Here stdnt_activity 5 mean -> Pending , 10 -> Inprogress, 15 means -> Closed
So, i want a query to dispaly the values of student table based on cndition.
the condition is for example:
The student kiran having stdnt_activity 5,10,15 where as sachin having stdnt_activity 5,10.
I need a query do diaplay all student records in a table which is having stdnt_activity 5,10 and not 15.
If student have stdnt_activity 5,10,15...we don't need to display to the user .
if student have stdnt_activity 5,10 then we need to display the values.
By above example i only need to diaplay the values of sachin,kumar, naveen (these student's doen's have 15).
while students kiran,venkat will have 5,10,15 ..so these records need to be ignored(no need to diaplay).
Thanks
Suresh
February 1, 2012 at 12:54 am
If you take a look at the first link in my signature, it'll show you how we prefer your sample stuff to be setup so we can use it instead of just admire it.
However, this appears to be a homework question, so, if you'll show your work so far, we can give you a hint to help you get where you need to be.
Btw, Urgent = Server going down due to overload, not new development query isn't behaving, and particularly not something that screams 'homework'.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 1, 2012 at 7:18 pm
Suresh,
Heh... ok... now you've found out two things. That the word "urgent" has become a bit of a taboo word on these forums and that you need to make life easier for people to help you. Please see the first link in my signature line below for how to get a whole lot better help a whole lot more quickly and without using the word "urgent" or any synonym of that word.
Since you're a relatively new person to the forum, I'll show you how to present data for a problem like this as well as the solution. As always, the details are in my code.
[font="Arial Black"]{EDIT} I DON"T KNOW WHAT I WAS THINKING [font="Arial"](I'll blame in the cold medicine I'm taking) [/font]BUT DON"T USE THE METHOD BELOW. IT'S TOO SLOW.[/font]
--===== Conditionally drop the test table to make reruns in SSMS easier.
-- This is NOT a part of the solution.
IF OBJECT_ID('tempdb..#Student','U') IS NOT NULL
DROP TABLE #Student
;
--===== Create the test table.
-- This is NOT a part of the solution.
CREATE TABLE #Student
(
stdnt_id INT,
stdnt_name VARCHAR(10),
stdnt_activity INT
)
;
--===== Populate the test table with data from the post.
-- This is NOT a part of the solution.
INSERT INTO #Student
(stdnt_id, stdnt_name, stdnt_activity)
SELECT '1','kiran','5' UNION ALL
SELECT '1','kiran','10' UNION ALL
SELECT '1','kiran','15' UNION ALL
SELECT '2','sachin','5' UNION ALL
SELECT '2','sachin','10' UNION ALL
SELECT '3','venkat','5' UNION ALL
SELECT '3','venkat','10' UNION ALL
SELECT '3','venkat','15' UNION ALL
SELECT '4','kumar','5' UNION ALL
SELECT '4','kumar','10' UNION ALL
SELECT '5','naveen','5' UNION ALL
SELECT '5','naveen','10'
;
--===== This is the proposed solution
-- It first finds only student activity codes of 5, 10, and 15
-- and the it aggregates the indivual student activity codes
-- into a single CSV grouped by the student info.
WITH
cteAggregate AS
(
SELECT s1.stdnt_id, s1.stdnt_name,
ActivityCSV = ','
+ (
SELECT CAST(s2.stdnt_activity AS VARCHAR(10)) + ','
FROM #Student s2
WHERE s2.stdnt_id = s1.stdnt_id
AND s2.stdnt_activity IN (5,10,15)
ORDER BY s2.stdnt_activity
FOR XML PATH('')
)
FROM #Student s1
GROUP BY s1.stdnt_id, s1.stdnt_name
)--==== This simply selects the students with only 5, 10 activity codes.
SELECT stdnt_id, stdnt_name
FROM cteAggregate
WHERE ActivityCSV = ',5,10,'
;
The next best thing for you to do is to play with it and see how it works so you can "pass it forward".
As a sidebar and I know you're probably not the one that did it but SQL Server can handle table and column names up to 128 characters. It's just not worth trying to save 2 characters by trying to abbreviate the word "student".
--Jeff Moden
Change is inevitable... Change for the better is not.
February 1, 2012 at 8:25 pm
Heh... Actually, that (the code above) takes 3-4 seconds on my machine on a mere million rows. That's just too slow. Here are two other solutions that work much faster (~1 second not including the seconds to build the million row test data). Please feel free to adapt them to meet your table and column naming...
--===== Do this in a nice, safe place that everyone has
USE TempDB
;
--===== Conditionally drop the test table to make reruns in SSMS easier.
-- This is NOT a part of the solution.
IF OBJECT_ID('dbo.Purchase','U') IS NOT NULL
DROP TABLE dbo.Purchase
;
--===== Create and populate the test table.
-- This is NOT a part of the solution.
SELECT TOP (1000000)
PurchaseID = IDENTITY(INT,1,1),
CustomerID = ABS(CHECKSUM(NEWID())) % 50000 + 1,
ItemCode = CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65)
INTO dbo.Purchase
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
--===== Add the expected PK
ALTER TABLE dbo.Purchase
ADD PRIMARY KEY CLUSTERED (PurchaseID)
;
CREATE INDEX IX_Purchase_CustomerID_ItemCode
ON dbo.Purchase (CustomerID, ItemCode)
;
--===== Find all customers that bought "A" and "B" but not "C".
SELECT CustomerID
FROM dbo.Purchase
WHERE ItemCode IN ('A','B')
GROUP BY CustomerID
HAVING COUNT(DISTINCT ItemCode) = 2
AND CustomerID NOT IN (SELECT CustomerID FROM dbo.Purchase WHERE ItemCode = 'C')
--===== Find all customers that bought "A" and "B" but not "C".
SELECT CustomerID
FROM dbo.Purchase
WHERE ItemCode IN ('A','B')
GROUP BY CustomerID
HAVING COUNT(DISTINCT ItemCode) = 2
EXCEPT
SELECT CustomerID FROM dbo.Purchase WHERE ItemCode = 'C'
;
;
--Jeff Moden
Change is inevitable... Change for the better is not.
February 2, 2012 at 12:29 am
Query to resolve
Hi,
I have one table student, i.e stdnt_id,stdnt_name,stdnt_activity
i have values in student table like this.
stdnt_id stdnt_name stdnt_activity
1 kiran 5
1 kiran 10
1 kiran 15
2 sachin 5
2 sachin 10
3 venkat 5
3 venkat 10
3 venkat 15
4 kumar 5
4 kumar 10
5 naveen 5
5 naveen 10
Here stdnt_activity 5 mean -> Pending , 10 -> Inprogress, 15 means -> Closed
So, i want a query to dispaly the values of student table based on cndition.
the condition is for example:
The student kiran having stdnt_activity 5,10,15 where as sachin having stdnt_activity 5,10.
I need a query do diaplay all student records in a table which is having stdnt_activity 5,10 and not 15.
If student have stdnt_activity 5,10,15...we don't need to display to the user .
if student have stdnt_activity 5,10 then we need to display the values.
By above example i only need to diaplay the values of sachin,kumar, naveen (these student's doen's have 15).
while students kiran,venkat will have 5,10,15 ..so these records need to be ignored(no need to diaplay).
Thanks
Suresh
Solution:
Using Common Table Expression it is very easy to find
WITH TAB1
AS
(
SELECT * FROM STUDENT
),
TAB2
AS
(
SELECT STDNT_NAME FROM TAB1 WHERE STDNT_ACTIVITY = 15
),
TAB3
AS
(
SELECT STDNT_NAME FROM TAB1
WHERE (STDNT_ACTIVITY =5) AND (STDNT_NAME NOT IN(SELECT STDNT_NAME FROM TAB2))
),
TAB4
AS
(
SELECT STDNT_NAME FROM TAB1
WHERE (STDNT_ACTIVITY =10) AND (STDNT_NAME NOT IN(SELECT STDNT_NAME FROM TAB2))
)
SELECT distinct T1.STDNT_NAME FROM TAB3 T1
JOIN TAB4 AS T2
ON T1.STDNT_NAME=T2.STDNT_NAME
I think there is no need to explain this.
Enjoy Friend
Regards
Prashant Goswami
February 2, 2012 at 7:51 am
prashantgoswami2007 (2/2/2012)
I think there is no need to explain this.
Actually, you might want to explain why you wrote code that takes 10 times longer to run, returns duplicate answers, can't be easily modified to solve for other similiar problems without adding extra lines of code, and hits the Student table 8 separate times. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 2, 2012 at 9:49 pm
Actually, you might want to explain why you wrote code that takes 10 times longer to run, returns duplicate answers, can't be easily modified to solve for other similiar problems without adding extra lines of code, and hits the Student table 8 separate times.
--Jeff Moden
--------------------------------------------------------------------------------
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".
Jeff, i know you are great in SQL server .. i had only given my opinion, i am on the learning process from last 2 month, I know my query takes long times,but i tried for this. Can you please explain the long process of my query.
Thanks jeff
February 2, 2012 at 10:07 pm
Try This SQL
create table student ( stdnt_id int, stdnt_name varchar(50),stdnt_activity SMALLINT )
go
INSERT INTO student
select 1, 'kiran', 5 UNION ALL
select 1 ,'kiran', 10 UNION ALL
select 1 ,'kiran', 15 UNION ALL
select 2 ,'sachin', 5 UNION ALL
select 2 ,'sachin', 10 UNION ALL
select 3 ,'venkat', 5 UNION ALL
select 3 ,'venkat', 10 UNION ALL
select 3 ,'venkat', 15 UNION ALL
select 4 ,'kumar', 5 UNION ALL
select 4 ,'kumar', 10 UNION ALL
select 5 ,'naveen', 5 UNION ALL
select 5 ,'naveen', 10
select * from student
WHERE stdnt_id NOT IN
(SELECT stdnt_id FROM student WHERE stdnt_activity =15 )
February 3, 2012 at 6:22 am
srikant maurya (2/2/2012)
Try This SQL
create table student ( stdnt_id int, stdnt_name varchar(50),stdnt_activity SMALLINT )
go
INSERT INTO student
select 1, 'kiran', 5 UNION ALL
select 1 ,'kiran', 10 UNION ALL
select 1 ,'kiran', 15 UNION ALL
select 2 ,'sachin', 5 UNION ALL
select 2 ,'sachin', 10 UNION ALL
select 3 ,'venkat', 5 UNION ALL
select 3 ,'venkat', 10 UNION ALL
select 3 ,'venkat', 15 UNION ALL
select 4 ,'kumar', 5 UNION ALL
select 4 ,'kumar', 10 UNION ALL
select 5 ,'naveen', 5 UNION ALL
select 5 ,'naveen', 10
select * from student
WHERE stdnt_id NOT IN
(SELECT stdnt_id FROM student WHERE stdnt_activity =15 )
Heh... you try it, srikant. What do you get for people that may only have a single "5"?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 3, 2012 at 6:29 am
prashantgoswami2007 (2/2/2012)
Actually, you might want to explain why you wrote code that takes 10 times longer to run, returns duplicate answers, can't be easily modified to solve for other similiar problems without adding extra lines of code, and hits the Student table 8 separate times.--Jeff Moden
--------------------------------------------------------------------------------
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".
Jeff, i know you are great in SQL server .. i had only given my opinion, i am on the learning process from last 2 month, I know my query takes long times,but i tried for this. Can you please explain the long process of my query.
Thanks jeff
It didn't sound like just an opinion especially when you write something arrogant sounding like "I think there is no need to explain this." Perhaps it's just a language barrier thing and I took it the wrong way. If so, I apologize.
The reason why the query you wrote takes so long is simply because it hits the same table 8 times when you really only need 2 (someone may even come up with a way to do it with 1 SELECT). Go back to the "customer" example I made see how to convert it to this problem (since it does sound like homework, I did want folks to have to work for it a bit). 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 3, 2012 at 8:58 am
Its simple:-
Select * from student where stdnt_id not in (select stdnt_id from student where stdnt_activity=15)
this will take sometime however your can easily optimize it but that I will leave to you....
February 3, 2012 at 11:02 am
ashutosh.kumarpandey (2/3/2012)
Its simple:-Select * from student where stdnt_id not in (select stdnt_id from student where stdnt_activity=15)
this will take sometime however your can easily optimize it but that I will leave to you....
It's not that simple. Have a student that only has a "5" and you'll see what I mean.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 3, 2012 at 11:22 am
create table student ( stdnt_id int, stdnt_name varchar(50),stdnt_activity SMALLINT )
go
INSERT INTO student
select 1, 'kiran', 5 UNION ALL
select 1 ,'kiran', 10 UNION ALL
select 1 ,'kiran', 15 UNION ALL
select 2 ,'sachin', 5 UNION ALL
select 2 ,'sachin', 10 UNION ALL
select 3 ,'venkat', 5 UNION ALL
select 3 ,'venkat', 10 UNION ALL
select 3 ,'venkat', 15 UNION ALL
select 4 ,'kumar', 5 UNION ALL
select 4 ,'kumar', 10 UNION ALL
select 5 ,'naveen', 5 UNION ALL
select 5 ,'naveen', 10 UNION ALL
select 6, 'test', 5
select * from student
WHERE stdnt_id NOT IN (SELECT stdnt_id FROM student WHERE stdnt_activity =15 )
I thought this simple query works fine. It does display all the records that Suresh ask for.
What was wrong if a student that only has a "5"? The record should be returned also, no?
February 4, 2012 at 7:50 am
Jing. (2/3/2012)
create table student ( stdnt_id int, stdnt_name varchar(50),stdnt_activity SMALLINT )go
INSERT INTO student
select 1, 'kiran', 5 UNION ALL
select 1 ,'kiran', 10 UNION ALL
select 1 ,'kiran', 15 UNION ALL
select 2 ,'sachin', 5 UNION ALL
select 2 ,'sachin', 10 UNION ALL
select 3 ,'venkat', 5 UNION ALL
select 3 ,'venkat', 10 UNION ALL
select 3 ,'venkat', 15 UNION ALL
select 4 ,'kumar', 5 UNION ALL
select 4 ,'kumar', 10 UNION ALL
select 5 ,'naveen', 5 UNION ALL
select 5 ,'naveen', 10 UNION ALL
select 6, 'test', 5
select * from student
WHERE stdnt_id NOT IN (SELECT stdnt_id FROM student WHERE stdnt_activity =15 )
I thought this simple query works fine. It does display all the records that Suresh ask for.
What was wrong if a student that only has a "5"? The record should be returned also, no?
No. The original problem was to return only those students who had 5 AND 10 but not 15. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 6, 2012 at 10:56 am
Then this will return only those students who had 5 AND 10 but not 15
create table student ( stdnt_id int, stdnt_name varchar(50),stdnt_activity SMALLINT )
go
INSERT INTO student
select 1, 'kiran', 5 UNION ALL
select 1 ,'kiran', 10 UNION ALL
select 1 ,'kiran', 15 UNION ALL
select 2 ,'sachin', 5 UNION ALL
select 2 ,'sachin', 10 UNION ALL
select 3 ,'venkat', 5 UNION ALL
select 3 ,'venkat', 10 UNION ALL
select 3 ,'venkat', 15 UNION ALL
select 4 ,'kumar', 5 UNION ALL
select 4 ,'kumar', 10 UNION ALL
select 5 ,'naveen', 5 UNION ALL
select 5 ,'naveen', 10 UNION ALL
select 6, 'test', 5 UNION ALL
select 7, 'test2', 10 UNION ALL
select 7, 'test2', 15
select * from student
WHERE stdnt_id NOT IN (SELECT stdnt_id FROM student WHERE stdnt_activity =15 )
and stdnt_id IN
(select stdnt_id from student GROUP BY stdnt_id having COUNT(*)=2)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply