|
student table
id stu
1 s1
2 s2
subject table
id sub
1 sub1
2 sub2
student-subject table
id sid subid marks
1 1 1 100
2 1 2 100
3 2 1 100
My question is what shall be the query for those students who did not appear in all exams
If you have an apple & I have an apple and we exchange our apples, then each of us will still have only one apple but if you have an idea & I have an idea and we exchange our ideas, then each of us will have two ideas!
|
|
|
|
|
SELECT stu
FROM student
WHERE id IN (
SELECT sid
FROM [student-subject]
GROUP BY sid
HAVING COUNT(*) < (SELECT COUNT(*) FROM subject)
)
|
|
|
|
|
Hello,
I have a table with one column "Product" which is primary column .
I want to make another table with fields TimePeriod, Qty and the products from Product table.
Product
Shirts
Trousers
Tie
Belts
Now I want to make this Table with following fields
TimePeriod Qty Shirts Trousers Tie Belts
Is there any query that I can write or I will have to hardcode the product names into the table
Regards
Pritha
|
|
|
|
|
Look up Pivot Tables.
Before you implement this, I suggest you decide why you want the table this way, and what might happen if you have, say, 40,000 records in your Products table.
|
|
|
|
|
Hello,
Thanks for your reply
But I am not going to have more than 10 products .
Is the Pivot Tables a link .
But it does not work
Regards,
Priya
|
|
|
|
|
prithaa wrote: But I am not going to have more than 10 products .
Is the Pivot Tables a link .
But it does not work
No, it's not a link - by look up, I meant Google it or BOL or some such reference.
|
|
|
|
|
Hi,
Here is a working code for your case using <big>PIVOT</big> and FOR XML PATH.
The list of columns is built with FOR XML PATH
Pivot columns are created <big>dynamically</big>, so doesn’t really matter how many columns (in your case – how many products) you have.
I hope this solve your problem. 
CREATE TABLE #p (ProdID int, ProdName nvarchar(20));
CREATE TABLE #s (TimePeriod int, Qty int, ProdID int);
insert into #p (ProdID, ProdName) select 1, 'Shirts';
insert into #p (ProdID, ProdName) select 2, 'Trousers';
insert into #p (ProdID, ProdName) select 3, 'Tie';
insert into #p (ProdID, ProdName) select 4, 'Belts';
insert into #s (TimePeriod, Qty, ProdID) select 20080705,138,1;
insert into #s (TimePeriod, Qty, ProdID) select 20080706,539,1;
insert into #s (TimePeriod, Qty, ProdID) select 20080707,313,1;
insert into #s (TimePeriod, Qty, ProdID) select 20080705,660,2;
insert into #s (TimePeriod, Qty, ProdID) select 20080706,370,2;
insert into #s (TimePeriod, Qty, ProdID) select 20080707,574,2;
insert into #s (TimePeriod, Qty, ProdID) select 20080705,764,3;
insert into #s (TimePeriod, Qty, ProdID) select 20080706,294,3;
insert into #s (TimePeriod, Qty, ProdID) select 20080707,202,3;
insert into #s (TimePeriod, Qty, ProdID) select 20080705,59,4;
insert into #s (TimePeriod, Qty, ProdID) select 20080706,247,4;
insert into #s (TimePeriod, Qty, ProdID) select 20080707,731,4;
DECLARE @cmd NVARCHAR(max);
SET @cmd = 'SELECT TimePeriod ' +
( SELECT ' , SUM(['+CONVERT(NVARCHAR,ProdID)+']) AS ['+ ProdName +']'
FROM #p AS p
FOR XML PATH('')
)
+
' FROM(
SELECT #s.TimePeriod, #s.Qty, #p.ProdID, #p.ProdName
FROM #p
INNER JOIN #s on #p.ProdID = #s.ProdID
) p
PIVOT (
SUM( Qty )
FOR ProdID IN
( [0] '+
( SELECT ' , ['+CONVERT(NVARCHAR,ProdID)+']'
FROM #p AS p
FOR XML PATH('')
)
+
' )
) AS pvt
GROUP BY TimePeriod
ORDER BY TimePeriod ; ' ;
PRINT @cmd;
EXEC(@cmd);
DROP TABLE #p;
DROP TABLE #s;
|
|
|
|
|
Hello,
Many many thanks for all your answers.Just a last question about the code
What does the following mean ?
select 20080705,138,1;
Regards,
Pritha
|
|
|
|
|
<br /> You have there a #s table defined like:
CREATE TABLE #s (TimePeriod <big>INT</big>, Qty <big>INT</big>, ProdID <big>INT</big>);
Then you have some dummy data inserts like
INSERT INTO #s ( TimePeriod, Qty, ProdID )
SELECT 20080705, 138, 1 ;
or (if you understand better this syntax)
INSERT INTO #s ( TimePeriod, Qty, ProdID )
VALUES ( 20080705, 138, 1 );
You can change TimePeriod field type to DATETIME or whatever suits your needs better.
Let me know if you have any difficulties understanding the code.
modified on Wednesday, July 9, 2008 10:56 AM
|
|
|
|
|
Hi,
In my table
Date Amount TotalAmount
01/07/2008 10000 10000
01/07/2008 10000 5000
01/07/2008 10000 0
I am using the following queries separately.
Select Date, Count(*), Sum(Amount) From Table Group by Date
Select Date, Count(*), Sum(Amount) From Table Where Amount>TotalAmount And TotalAmount <> 0 Group by Date
Select Date, Count(*), Sum(Amount) From Table Where Amount>TotalAmount And TotalAmount = 0 Group by Date
How can I execute the above 3 mentioned separate queries in a single query to get the result like
Date TotalRecords Amount1 TotalRec Amount2 Tot Amount2
01/07/2008 3 30000 1 10000 1 1000
Tnx in adv.
Balasubramanian K.
|
|
|
|
|
BalasubramanianK wrote: In my table
Date Amount TotalAmount
01/07/2008 10000 10000
01/07/2008 10000 5000
01/07/2008 10000 0
I am using the following queries separately.
Select Date, Count(*), Sum(Amount) From Table Group by Date
Select Date, Count(*), Sum(Amount) From Table Where Amount>TotalAmount And TotalAmount <> 0 Group by Date
Select Date, Count(*), Sum(Amount) From Table Where Amount>TotalAmount And TotalAmount = 0 Group by Date
How can I execute the above 3 mentioned separate queries in a single query to get the result like
Date TotalRecords Amount1 TotalRec Amount2 Tot Amount2
01/07/2008 3 30000 1 10000 1 1000
You are probably looking for something like this[^]
|
|
|
|
|
How to write the SQL DB connection string in namespace file.Then i wanna add that connection string like "using DBconn;" <<DbConn should be connection string>>
If you know that how to write DB Connection string using webconfig file also welcome.
BD is MSSQL 2005
Programing language : C#
Web development using ASP.Net
If some one know please reply me
Thank you..!!!
ReGRdZZZ
CheeN
|
|
|
|
|
|
difference between sql server 2000 and 2005, i am going to for quick review, if any once can tell me in simplified manner i will really appreciate him/her.
please max avoid to give links. please give quick reference differences between those
This is haneef.............................................................
|
|
|
|
|
Hi Good Guys!
I want to write a trigger which can iterate through each udpated column and check for a string match. if found then I need to rollback.
TIA
puranonnet@hotmail.com
www.ibrinte.com
|
|
|
|
|
|
Hello,
I have written following query.
select em.ticketid, em.tickettitle, em.handlerid, em.CUSTOMERID, em.departmentid, em.site, em.platform,
em.server, em.swrelease, em.generateddate, em.emergencyraiseddate, em.startdate, em.enddate, em.problemdesc,
em.rootcause, em.customerimpact, em.outageevidence, em.isfirsttime, em.workdone, em.techanalysis,
em.plannedaction, em.permanentfix, em.attid, e.EMPNAME, dept.DEPARTMENTNAME, cust.CNAME from emergencycallup em,
employee e, department dept, customer cust where em.DEPARTMENTID = '1' and em.handlerid ='test.emp' and
em.handlerid = e.empid and cust.customerid = em.customerid and dept.departmentid = em.departmentid
But this retrieves same row multiple times. If I simplify this to,
select em.ticketid, em.tickettitle, em.handlerid, em.CUSTOMERID, em.departmentid, em.site, em.platform,
em.server, em.swrelease, em.generateddate, em.emergencyraiseddate, em.startdate, em.enddate, em.problemdesc,
em.rootcause, em.customerimpact, em.outageevidence, em.isfirsttime, em.workdone, em.techanalysis,
em.plannedaction, em.permanentfix, em.attid from emergencycallup em where em.DEPARTMENTID = '1' and em.handlerid
='test.emp'
one record is retrieved only once. But here I'm loosing some details such as name.
How to modify this query so that I retrieve all data but records are not duplicated.
TIA,
Puneri
modified on Sunday, July 6, 2008 9:30 AM
|
|
|
|
|
There are 4 tables.
Emergencycallup contains employee id(handlerid), customer id, department id
for these 3 columns i've to get employee name, customer name and department name from table employee, customer and department respectively.
|
|
|
|
|
Hi
The two queries should return the same row count.
Examine the employee, customer and department table for duplicates.
Have you used primary key and foreign key constraints ?
Kjetil
|
|
|
|
|
hi
how can i transfering data of table with script?
|
|
|
|
|
rezarafiee wrote: how can i transfering data of table with script?
Write script to read data, and write a script to write data. Connect those 2 and wolla.
|
|
|
|
|
You can do this without a script.
INSERT INTO table2 (field1, field2, ..., fieldn) SELECT ..... FROM table1
|
|
|
|
|
Dear All,
I need to send an email every 30 minutes if Insert happenned...Any one plz can help???
I need this to be inside the SQL server 2005, this means when an Insert happens on 'myTable' and depending on some time, an Email is being sent to a specified person.
I just do not know where to go to implement such idea... 
I searched over the Internet, but nothing is useful for this purpose.
Kind Regards
OBarahmeh
|
|
|
|
|
SQL Server has email functionality.[^] You need to setup a job that runs in every 30 minutes, checks for inserts and sends mail.
|
|
|
|
|
Dear Navaneeth,
I know there is email functionality in SQL server, and I tried it...but I could not make the scenario that check If Insert has happenned...
So I need a sample of how to check for Inserts on myTable, then I can do the part of sending email..
Also I need to send the last inserted rows on myTable as attachement or HTML inside the email sent.
Kind Regards
Kind Regards
OBarahmeh
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
