Where If

I have 3 fields that I need to match in two tables in MSSQL. Here is the layout:

Table 1:
StreetNumber
Street
FullAddress
Table 2:
StreetNumber
Street
MailAddress (its StreetNumber + Street)

Now for some stupid reason that I can only shake my head at in table 2 if the mailing address is the same as the street address, then the StreetNumber and Street are not populated. Also people might have the same mailing address for multiple address. So a simple OR is not working.

What I need is some like:

SELECT * FROM table1 WHERE (
If StreetNumber <> '' Then
table1.StreetNumber = table2.StreetNumber AND table1.Street = table2.Street
ELSE
table1.FullAddress = table2.MailAddress
END)

Any help?

The "easy" (but not so pretty) way would be to embed the entier sql in a string and then depending on "IF" add what you need.

something along this line:

DECLARE @SQLStatement nvarchar(2000)

SET @SQLStatement = 'SELECT * FROM table1 WHERE ('
IF StreetNumber <> '' BEGIN
SET @SQLStatement = @SQLStatement + 'table1.StreetNumber = table2.StreetNumber AND table1.Street = table2.Street'
END
ELSE BEGIN
SET @SQLStatement = @SQLStatement + 'table1.FullAddress = table2.MailAddress
END

EXEC(@SQLStatement)

This is a quick n' dirty way around your problem