T-SQL question

[ThomasSoerensen]

Hi guys

I have a view (A) looking into another view (B) and counting the number of records in (B) and reporting the number of rows in (B) in (A).
when there are 2 records in (B) the output of the view is as follows

Column1 | Column2 | Column3 | Column4
something|something| 2|something

when there are 0 records in (B) want the output as
Column1 | Column2 | Column3 | Column4
something|something| 0|something

but I just get an empty result screen in MS SQL Server Management Studio

do you guys need something I could add to the T-SQL of view (A) that would write the output columns with a 0 in Column3 if there are no records in view (A)?

 

[HairyArsedBloke]

Have you tried left joins & the IsNull function ?

 

[ThomasSoerensen]

Have you tried left joins & the IsNull function ?

I am trying the ISNULL function now. I will tell you the result in circa 15 min as that is how long this query takes.

 

[Durbs]

Remember to alter the join too or you'll still get nowt!

 

[MarillionFan]

Use a case statement with an isnull and the count. That way you can control the count and return a 0 if the join is null

 

[ThomasSoerensen]

Remember to alter the join too or you'll still get nowt!

thanks guys - it works now.

where do I send the beers?

 

[ThomasSoerensen]

Remember to alter the join too or you'll still get nowt!

how do you know what join I have?

I am very impressed

 

[Durbs]

how do you know what join I have?

Its chilly out here on your balcony, any chance of passing some cheese on toast out (dash of Lea & Perrins).

Cos if you'd used a left join originally your result would have been

Column1 | Column2 | Column3 | Column4
something|something| NULL|something

But as you said you were getting a blank query window it suggested you were using an INNER which would return nothing instead of a NULL.

 

[ThomasSoerensen]

Its chilly out here on your balcony, any chance of passing some cheese on toast out (dash of Lea & Perrins).

Cos if you'd used a left join originally your result would have been

Column1 | Column2 | Column3 | Column4
something|something| NULL|something

But as you said you were getting a blank query window it suggested you were using an INNER which would return nothing instead of a NULL.

Geek!!!!!
Very impressed.

 

[Spacecadet]

I am trying the ISNULL function now. I will tell you the result in circa 15 min as that is how long this query takes.

15 minutes!!!!!!

I hope your query is processing a few 10's of million rows or the server is extremely under specced the only other option is that you've written some really bad SQL