Want a single query of these two.

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

Transact-SQL (2005)

Want a single query of these two.

Author

Topic

vijayrawatsan
Starting Member

18 Posts

Posted - 2010-01-28 : 11:01:45

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
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-28 : 11:36:13

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

vijayrawatsan
Starting Member

18 Posts

Posted - 2010-01-28 : 12:27:44

Thanks for the reply but its not working cant oyu simply union the answers of the two queries??

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-28 : 12:34:41

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.

nitookatyal
Starting Member

4 Posts

Posted - 2010-01-29 : 05:56:40

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources