SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Want a single query of these two.		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vijayrawatsan
Starting Member

India
18 Posts
Posted - 01/28/2010 :  11:01:45  Show Profile  Reply with Quote
Query 1) It returns two columns. First Name and Second Roll

String query1 = "select users.name Name,students.class_roll Roll from users,students, (select distinct(student_id) sid from att where course_id='" + DD_Chosecourse_view.SelectedValue + "' AND subject_id='" + DD_subject_view.SelectedValue + "') s WHERE s.sid=users.user_id AND s.sid=students.student_id order by students.class_roll";




Query 2) It return only one column Date.

String strqr = "select count(case when a.Attendance='true' then date else null end) Date from att a inner join students s on a.student_id = s.student_id where a.subject_id='" + DD_subject_view.SelectedValue + "' AND a.course_id='" + DD_Chosecourse_view.SelectedValue + "' Group By s.class_roll order By s.class_roll";


I want a single query that will return three columns Name,Roll and Date...please help.

Hoping for a quick reply.

:)

Thanks.

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts
Posted - 01/28/2010 :  11:36:13  Show Profile  Reply with Quote
Try this...
select a.name as [Name]
,b.class_roll as [Roll]
,count(case when c.Attendance='true' then c.date else null end) as [Date]
from usera a
inner join students b on b.student_id = a.user_id
inner join att c on c.student_id = b.student_id
where a.subject_id='" + DD_subject_view.SelectedValue + "' 
AND a.course_id='" + DD_Chosecourse_view.SelectedValue + "'
group by a.name,b.class_roll
order by b.class_roll
Go to Top of Page

vijayrawatsan
Starting Member

India
18 Posts
Posted - 01/28/2010 :  12:27:44  Show Profile  Reply with Quote
Thanks for the reply but its not working cant oyu simply union the answers of the two queries??
Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts
Posted - 01/28/2010 :  12:34:41  Show Profile  Reply with Quote
Are you getting an error? What error is it?
Show us table structure,sample data and expected ouput...we can provide you a quick solution then.
Go to Top of Page

nitookatyal
Starting Member

India
4 Posts
Posted - 01/29/2010 :  05:56:40  Show Profile  Reply with Quote
Hi,
try this...

select users.name Name,students.class_roll Roll,s.Date from users,students,
(select distinct(student_id) sid,count(case when a.Attendance='true'
then date else null end) Date from att where course_id='" + DD_Chosecourse_view.SelectedValue + "' AND subject_id='"
+ DD_subject_view.SelectedValue + "') s
WHERE s.sid=users.user_id AND s.sid=students.student_id order by students.class_roll";



Nitoo katyal
Software Engineer
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000