joining two sql queries

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

First query as follows

SQL

select cmn_minor_code as Course_Name, convert(char,cbm_batch_start_dt,106) as Course_date, cbm_batch_id as Batch_ID
from co_batch_master
where cbm_active <. 'd'
and cbm_batch_start_dt between convert(datetime,'06-Jul-2015',6)
and convert(datetime,'06-Jul-2015',6)  order by cbm_batch_start_dt

When i run the first query output as follows

Course_Name   Course_date            Batchid

 AFF            12   jun 2015          B8753

Second query as follows

SQL

select count(*)  as No_of_students
from student s,course_registration cr,batch_course_registration bcr,co_batch_master cbm where cr.stud_id = s.stud_id and cr.cr_bill_no = bcr.cr_bill_no and bcr.bcr_batch_id = cbm.cbm_batch_id and cbm.cbm_active <> 'D' and cr.cr_active = 'A'  and s.stud_active <>'D' and cbm_batch_id = 'B8753'

When i run the second query output as follows

No_of_students  
24

I want to combine the first query and second query and output i want as follows

Course_Name   Course_date  Batchid    No_of_students

 AFF          12 jun 2015   B8753        24

for that how can i combine the above first and second query and get the output in sql server.

[edit]Code block added - OriginalGriff[/edit]

Posted 11-Jul-15 2:48am

narasiman1

Updated 11-Jul-15 3:15am

OriginalGriff

v2

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-07-11T04:55:00

If I understood your question correctly, you're after GROUP BY[^] clause.

If you take the first query and join the tables to the tables in the second and then group by all non-aggregated columns, I believe that we're getting close to what you want. Also all of the conditions need to be merged into the same statement.

So perhaps something like this:

SQL

SELECT  cbm.cmn_minor_code as Course_Name, 
        CONVERT(char,cbm.cbm_batch_start_dt,106) as Course_date, 
        cbm.cbm_batch_id as Batch_ID,
        count(*)  as No_of_students
FROM co_batch_master cbm,
     student s,
     course_registration cr,
     batch_course_registration bcr
WHERE cbm.cbm_active   < 'd'
AND   cbm.cbm_batch_start_dt 
      BETWEEN CONVERT(datetime,'06-Jul-2015',6) 
      AND     CONVERT(datetime,'06-Jul-2015',6)  
AND   cr.stud_id       = s.stud_id 
AND   cr.cr_bill_no    = bcr.cr_bill_no 
AND   bcr.bcr_batch_id = cbm.cbm_batch_id 
AND   cbm.cbm_active   <> 'D' 
AND   cr.cr_active     = 'A'  
AND   s.stud_active    <>'D' 
AND   cbm_batch_id     = 'B8753'
GROUP BY cbm.cmn_minor_code, 
        CONVERT(char,cbm.cbm_batch_start_dt,106),
        cbm.cbm_batch_id
ORDER BY cbm_batch_start_dt

Note that I'm really not sure about the joins nor the other conditions since I have no idea about your table structure or logic. However, this should get you started.

joining two sql queries

1 solution

Solution 1

Add your solution here

Preview 0

joining two sql queries

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us