Combine columns of two queries

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)

Combine columns of two queries

Author

Topic

vijayrawatsan
Starting Member

18 Posts

Posted - 2009-12-31 : 01:47:01

Let me keep it simple..

Below are two queries::
1) The first one is to retrieve name and roll numbers of students
(i.e. two columns)

(select users.name Name,students.class_roll Roll from users,students, (select distinct(student_id) sid from att where course_id=@cid AND subject_id=@subid) s WHERE s.sid=users.user_id AND s.sid=students.student_id order by students.class_roll ) ,

2) The second query retrieves the classes taken by each student.
(i.e. one column)

( 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=@subid AND a.course_id=@cid Group By s.class_roll order By s.class_roll)

I want a single query that have three columns generated by above two queries as it is.
(i.e. name, roll , attendance)

I hope it is possible. Thank you all.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-31 : 02:02:53

Try this...

tab1.Name,tab1.Roll,tab2.Date from
(select users.name Name,students.class_roll Roll,student_id from users,students, (select distinct(student_id) sid from att where course_id=@cid AND subject_id=@subid) s WHERE s.sid=users.user_id AND s.sid=students.student_id order by students.class_roll ) tab1
inner join
( select count(case when a.Attendance='true' then date else null end) Date,
s.student_id from att a inner join students s on a.student_id = s.student_id where a.subject_id=@subid AND a.course_id=@cid Group By s.class_roll order By s.class_roll) tab2 on tab1.student_id=tab2.student_id

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2009-12-31 : 02:18:19

[code]
select users.name Name,s.class_roll Roll,student_id from users inner join
(
select distinct(student_id) sid,
count(case when att.Attendance='true' then date else null end) Date,
students.class_roll
from att inner join students on att.student_id=students.student_id
where course_id=@cid AND subject_id=@subid Group By students.class_roll

) s On s.sid=users.user_id order by s.class_roll
[/code]

PBUH

vijayrawatsan
Starting Member

18 Posts

Posted - 2009-12-31 : 02:59:44

Senthil
In your query it is saying order by is not allowed in VIEWs.

Idera
In your query it is saying student_id is ambiguous/invaalid.

Sachin.Nand

2937 Posts

Posted - 2009-12-31 : 03:02:14

select users.name Name,s.class_roll Roll,s.sid as student_id from users inner join
(
select distinct(students.student_id) sid,
count(case when att.Attendance='true' then date else null end) Date,
students.class_roll
from att inner join students on att.student_id=students.student_id
where course_id=@cid AND subject_id=@subid Group By students.class_roll

) s On s.sid=users.user_id order by s.class_roll

PBUH

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-31 : 04:03:13

quote:
Originally posted by vijayrawatsan

Senthil
In your query it is saying order by is not allowed in VIEWs.

Idera
In your query it is saying student_id is ambiguous/invaalid.

Remove the order by class

tab1.Name,tab1.Roll,tab2.Date from
(select users.name Name,students.class_roll Roll,student_id from users,students, (select distinct(student_id) sid from att where course_id=@cid AND subject_id=@subid) s WHERE s.sid=users.user_id AND s.sid=students.student_id) tab1
inner join
( select count(case when a.Attendance='true' then date else null end) Date,
s.student_id from att a inner join students s on a.student_id = s.student_id where a.subject_id=@subid AND a.course_id=@cid Group By s.class_roll) tab2 on tab1.student_id=tab2.student_id

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources