Chris McGrath’s Blog

Just another Developer’s Blog

Posts Tagged ‘tip

A really sneaky SQL join problem

leave a comment »

I was creating a query the other day and it wasn’t returning the amount of rows I was expecting. I did quickly figure out what was causing me but how to solve it stumped me. Luckily a co-worker who was much more experienced with SQL knew how to fix it. But it’s a case that those of us who don’t live and breath SQL probably don’t know about and it’s so easy to get this wrong all developers should be aware of it.

Here’s the problem…

You have a person table and a phone number table. A phone can be work or home. In the person table there is John, Michael, Chris and Dan. John has neither number entered. Michael has both entered, Chris just has work number entered and Dan has home number entered.

You want to display a list of every Person and if they have their work number entered, to grab that as well, else null.

Easy right? How about giving it a go?

Did you come up with this…

select Person.*, Phone.Number
from Person left outer join Phone on person.id = phone.personid
where Phone.Type = ‘w’ or Phone.Type is null

That was easy, the left outer join stops the person row from disappearing if no phone number exists and we get rid of the home numbers through the where – we are also careful not to remove the items without a phone number with a “OR IS NULL” check.

Well you’re wrong. If you were to run it only 3 of the 4 people would show up. Can you think why? Here’s a clue, the person would be Dan.

If you still haven’t got it, the Dan rows that exist before the where is just when type = ‘h’. It doesn’t have a null value their like John has. So the where then says, “is it w? is it null? well get rid of it.”

But how do we fix this? It doesn’t look like we can, our check is happening too late we want to filter the phone table and then outer join it. Not possible? Must require some complex subquery? Well actually it doesn’t…

select Person.*, Phone.Number
from Person left outer join Phone on person.id = phone.personid
AND Phone.Type = ‘w’

That’s it. We move the check inside the join.

It’s quite a nice solution for it really. I think as long as you are aware of this you won’t have any problem but I do wonder how many bugs are out there exist because this wasn’t taken into consideration.

Written by Chris McGrath

July 17, 2009 at 9:10 am

Posted in Blog

Tagged with ,

Keeping Names positive

leave a comment »

Just read this blog – http://www.markhneedham.com/blog/2009/06/11/coding-keep-methodvariable-names-positive/

This is something that has always annoyed me too. Things like e.Cancel = true; just seem wrong.

The absolute worst case of this I have ever come across is in the Local Security Policy. In security options, there’s a policy of, “Domain member: Disable machine account password changes” which you can set to Enabled or disabled.

So you can enable the disabling of the machine account password changes or disable the disabling of the machine account password change.  This is just mind-boggling.

Written by Chris McGrath

July 10, 2009 at 9:53 am

Posted in Blog

Tagged with ,

Tip: Remember using System.Linq

leave a comment »

I was updating an old file that hadn’t been touched since dotNet 2 and wanted to use the Linq extension method ‘Select’ method. But it wasn’t showing up. I checked to make sure the assembly was dotNet 3.5 and it had a reference to System.Core. I was stumped.

Luckily it eventually came to me that I was missing System.Linq from my using list. But it’s an easy thing to miss and the Smart Tags don’t detect it. Maybe there should be an option to make it a warning if a file is missing it.

Written by Chris McGrath

April 16, 2009 at 1:02 pm

Posted in Blog

Tagged with , ,