-2

I had a scenario which I need to write a query.

I had a Query1 which return hundreds of records with these four column names (batch_no, batch_name, class_no, class_name) based on some joins. I need to write a final query (Query2) to return columns from other tables which uses class_no and batch_no column values returned by Query1.

My final query (query2) should return these columns for the all records returned by the query1: 

Batch_global_Id (in table global_details), 
batch_no, batch_name, class_no, class_name, 
Start_year, End_year (from table time_details)

Global_details has class_no column so that we can join the results of query1 with Global_details to get the values for the column Batch_global_Id in the final query (Query2).

But the problem here is to get the Start_year and End_year values from the table time_details. We need get the two field values for all the records return by query1 based on class_no, batch_no.

Here is the condition to get those values (Start_year, End_year).

  • If there is a record in time_details table with that class_no, we need to get Start_year and End_year from that record in the final output query.

  • If there is no record in time_details table with that class_no, then get the Start_year and End_year values from the record with matching batch_no in the time_details table.

  • If neither exists (NO record in time_details table with the matching class_no & batch_no), then get the Start_year and End_year values from the record in time_details with batch_no = null

Can some one help me in writing this query?

Edit as per comment

Select 
    batch_no, batch_name, class_no, class_name 
into 
    #temptable 
from 
    ... (query1) ...

Expected query

Select 
    gd.Batch_global_Id, 
    tt.batch_no, tt.batch_name, tt.class_no, tt.class_name, 
    td.Start_year, td.End_year 
from 
    Global_details gd 
inner join 
    time_details td on gd.class_no = td.class_no 
inner join 
    **something like to get values from time_details table based on the above condition//**
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459

2 Answers2

0

Hard to be specific without sample data, but this should get you on the right track. Depending on the size of the tables, it might be faster to select the start_year and end_year where batch_no is null into variables first, then insert those variables at the end of the coalesce, rather than adding the last join.

Select a.batch_no, a.batch_name, a.class_no, a.class_name
    , b.Batch_global_Id 
    , coalesce(c.Start_year, d.Start_year, e.Start_year) as StartYear
    , coalesce(c.End_year, d.End_year, e.End_year) as EndYear
from [Query1Results] a
left join Global_details b
on a.class_no = b.class_no
left join time_details c
on a.class_no = c.class_no
left join time_details d
on a.batch_no = d.batch_no
left join time_details e
on e.batch_no is null
APH
  • 4,109
  • 1
  • 25
  • 36
  • Hello, Thanks for your answer. I'm facing issue with your suggested solution. It is returning more more records than my Query1 results. It basically attach some other columns from other tables to the same records return by the Query1. But these joins are returning lot of records. Any suggestion please –  Sep 01 '15 at 23:52
  • If you have more than one record that matches on your join criteria, @abhi, then you will need to add other criteria to the join to narrow it down so that the join is 1 to 1 or less. If what is returned are exact duplicates, try adding `distinct` after `select.` – APH Sep 02 '15 at 23:54
0

please try this

Select 
    gd.Batch_global_Id, 
    tt.batch_no, tt.batch_name, tt.class_no, tt.class_name, 
    td.Start_year, td.End_year 
from 
    Global_details gd, time_details td, temp_table tt
where tt.class_no = gd.class_no
and tt.class_no = td.class_no

union

Select 
    gd.Batch_global_Id, 
    tt.batch_no, tt.batch_name, tt.class_no, tt.class_name, 
    td.Start_year, td.End_year 
from 
    Global_details gd, time_details td, temp_table tt
where tt.class_no = gd.class_no
and tt.batch_no = td.batch_no
and tt.class_no not in (Select tt.class_no from 
    Global_details gd, time_details td, temp_table tt
where tt.class_no = gd.class_no
and tt.class_no = td.class_no)

union 

Select 
    gd.Batch_global_Id, 
    tt.batch_no, tt.batch_name, tt.class_no, tt.class_name, 
    td.Start_year, td.End_year 
from 
    Global_details gd, time_details td, temp_table tt
where tt.class_no = gd.class_no
and td.batch_no is null
and tt.batch_no not in (Select td.batch_no 
from 
    Global_details gd, time_details td, temp_table tt
where tt.class_no = gd.class_no
and tt.batch_no = td.batch_no
and tt.class_no not in (Select tt.class_no from 
    Global_details gd, time_details td, temp_table tt
where tt.class_no = gd.class_no
and tt.class_no = td.class_no))

here is my logic

I'm dividing query into three parts based on your requirement

first query gives results with class_no condition

second query gives batch_no condition and excluding the records which are from first query

the same way third query with batch_no nu condition and excluding the records which are from second query.

If you could provide me data it would be better for testing.

i know this causes performance issues when your records are more, but you could try this as of now.

Suresh
  • 16
  • 1