Hi,
I am working on a query and tbales have the following structure 

Table1 - columns , ID1, ID2
Table2 - columns   ID2,ID3
Table3 - columns   ID3, ID4
Table4 - columns   ID4,ID5
Table5 - columns   ID5, COL2

My requirement is to get following

ID1,[ Flag if there exists a record in Table5 corresponding that ID1 Table1 , [ i'e from joining Table 1 till Table 5 ]

I am expecting output like

ID1, FLAG

1, Y
2, Y
3, N

where Y is when record exists in Table5
      N is when record is missing in table 5

I can join tables but cannot include exists or case statement as there are multiple joins involved
Plz suggest

SELECT ID1,
       CASE WHEN EXISTS (SELECT *
                         FROM   tbl2
                         JOIN   tbl3 ON tbl3.ID3 = tbl2.ID3
                         JOIN   tbl4 ON tbl4.ID4 = tbl3.ID4
                         JOIN   tbl5 ON tbl5.ID5 = tbl4.ID5
                         WHERE  tbl2.ID1 = tbl1.ID1)
            THEN 'Y'
            ELSE 'N'
       END AS Flag
FROM   tbl1

Why can't you use case, e.g.,

Select t1.ID1, Case When t5.ID5 Is Null Then 'N' Else 'Y' End As Flag
From Table1 t1
Left Outer Join Table2 t2 On t1.ID2 = t2.ID2
Left Outer Join Table3 t3 On t2.ID3 = t3.ID3
Left Outer Join Table4 t4 On t3.ID4 = t4.ID4
Left Outer Join Table5 t5 On t4.ID5 = t5.ID5;

• Marked as answer by NZ-D 8 hours 20 minutes ago

Why can't you use case, e.g.,

Select t1.ID1, Case When t5.ID5 Is Null Then 'N' Else 'Y' End As Flag
From Table1 t1
Left Outer Join Table2 t2 On t1.ID2 = t2.ID2
Left Outer Join Table3 t3 On t2.ID3 = t3.ID3
Left Outer Join Table4 t4 On t3.ID4 = t4.ID4
Left Outer Join Table5 t5 On t4.ID5 = t5.ID5;

• Marked as answer by NZ-D Sunday, July 19, 2015 10:46 PM

Why can't you use case, e.g.,

Select t1.ID1, Case When t5.ID5 Is Null Then 'N' Else 'Y' End As Flag
From Table1 t1
Left Outer Join Table2 t2 On t1.ID2 = t2.ID2
Left Outer Join Table3 t3 On t2.ID3 = t3.ID3
Left Outer Join Table4 t4 On t3.ID4 = t4.ID4
Left Outer Join Table5 t5 On t4.ID5 = t5.ID5;

• Marked as answer by NZ-D Sunday, July 19, 2015 10:46 PM

Why can't you use case, e.g.,

Select t1.ID1, Case When t5.ID5 Is Null Then 'N' Else 'Y' End As Flag
From Table1 t1
Left Outer Join Table2 t2 On t1.ID2 = t2.ID2
Left Outer Join Table3 t3 On t2.ID3 = t3.ID3
Left Outer Join Table4 t4 On t3.ID4 = t4.ID4
Left Outer Join Table5 t5 On t4.ID5 = t5.ID5;

• Marked as answer by JOHN-ugrai Sunday, July 19, 2015 10:46 PM

Why can't you use case, e.g.,

Select t1.ID1, Case When t5.ID5 Is Null Then 'N' Else 'Y' End As Flag
From Table1 t1
Left Outer Join Table2 t2 On t1.ID2 = t2.ID2
Left Outer Join Table3 t3 On t2.ID3 = t3.ID3
Left Outer Join Table4 t4 On t3.ID4 = t4.ID4
Left Outer Join Table5 t5 On t4.ID5 = t5.ID5;

• Marked as answer by JOHN-ugrai Sunday, July 19, 2015 10:46 PM

Hi,

Thanks for the replies I missed the requirement

Table1 t1- columns , ID1, ID2
Table2 t2- columns   ID2,ID3
Table3 t3- columns   ID3, ID4
Table4 t4 - columns   ID4,ID5
Table5 t5- columns   ID5, COL2
Left Join TABLE4 [alias t44]    - on t4.ID4 = T44.ID5 
My requirement is to get following

ID1,[ Flag if there exists a record in Table4 [t44]  corresponding that ID1 Table1 ,
I am expecting output like

ID1, FLAG

1, Y
2, Y
3, N

where Y is when record exists in Table4
      N is when record is missing in table4
Issue is in TABLE 4. It has 2 columns 
ID4,ID5 [ for a single ID1 there can be multiple rows like mentioned below 
1,3
3,5
5,NULL 
Now I am using  the suggested query with 
-Case When t5.ID5 Is Null Then 'N' Else 'Y' End As Flag
Based on the data in TABLE4 ..It is evaluating value of FLAG as  both Y,N , 
I get two rows for ID1 as 
ID5 is both NULL and not NULL for  a single ID1 [ TABLE1]

plz suggest

Hi NZ-D,

Have no idea on why you have to use another LEFT JOIN "Left Join TABLE4 [alias t44]    - on t4.ID4 = T44.ID5 ". In your case if there exists more than one row in table4 for a single ID1 in table5, a group by as below would get the expected output flags indicating if any corresponding row exists in table5 for a ID1 in table1. 

;WITH Cte AS(
Select t1.ID1, Case When t5.ID5 Is Null Then 'N' Else 'Y' End As Flag
From Table1 t1
Left Outer Join Table2 t2 On t1.ID2 = t2.ID2
Left Outer Join Table3 t3 On t2.ID3 = t3.ID3
Left Outer Join Table4 t4 On t3.ID4 = t4.ID4
Left Outer Join Table5 t5 On t4.ID5 = t5.ID5
)
SELECT ID1,MAX(Flag) FROM Cte GROUP BY ID1

Your requirement would be much more precise if you could post some sample data and expected output.

If you have any question, feel free to let me know.

Now I am using the suggested query with

-Case When t5.ID5 Is Null Then 'N' Else 'Y' End As Flag
Based on the data in TABLE4 ..It is evaluating value of FLAG as  both Y,N , 
I get two rows for ID1 as 
ID5 is both NULL and not NULL for  a single ID1 [ TABLE1]

So what happens if  you use the query I suggested? It will certainly not output two rows for the same ID!

Generally, for this type of questions, it helps immensely if you post:

1. CREATE TABLE statements for your tables.
2. INSERT statements with sample data, enough to illustrate the problem.
3. The desired result given the sample.

When you only post a loose description like you did, answers tend to be built on guesswork.

Hi Eric

Thanks .This is exactly what I <g class="gr_ gr_36 gr-alert gr_gramm Punctuation multiReplace" data-gr-id="36" id="36">wanted .</g>Another join with t4 is required as there is a hierarchy  in t4.

I know this is hard to do in 1 <g class="gr_ gr_131 gr-alert gr_spell ContextualSpelling ins-del multiReplace" data-gr-id="131" id="131">sql</g> statement but CTE and max it worked 

Hi, I have created a view on above CTE but I cant create an
 indexed view as it has CTE and using joins. Any tips for that 
Also all underlying tables already have primary key constraint 
on ID columns so any other approach which can help ?
I am thinking of a query using some rownumber/partition and getting first row based on order of FLAG

• Edited by JOHN-ugrai 23 minutes ago

Hi, I have created a view on above CTE but I cant create an
 indexed view as it has CTE and using joins. Any tips for that 
Also all underlying tables already have primary key constraint 
on ID columns so any other approach which can help ?
I am thinking of a query using some rownumber/partition and getting first row based on order of FLAG

• Edited by JOHN-ugrai Tuesday, July 28, 2015 6:44 AM

Why would you create an indexed view in the first place?

I don't think the CTE as such is an impediment for indexing the view, but the left joins are.

Hi,

Indexed view is required to speed up the view performance as it references 5 tables although 
the join is on primary keys but the data is huge. I have tried modifying it using

1:-Removing CTE, removing distinct, using MAX in the first query itself and adding a group by

2:-Using partition by clause , getting first row from the partition using derived table .

But No end to the issues as indexed view cannot be created when distinct, CTE,MAX is used

• Edited by JOHN-ugrai 7 hours 47 minutes ago