SQL Join with PropertyID and Name

Question

I am still fairly green when it comes to SQL joins. The below code works but I want to check that I am doing it the best way before I copy and paste errors into other work in the future.

The idea of the below code is to get the PropertyID and Name of a property from the first Table and using the PropertyID join them. In this case I am actually using every field in Table B (thus the *).

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SQL %>"
    SelectCommand="      
        select TblA.PropertyID as PId, TblA.Propertyname as PNa, TblB.*  
        from TbPropertyDetails as TblA 
            inner join TbPropertyDetailsSafeguarding as TblB on TblA.PropertyID = TblB.PropertyID 
       where TblA.RegionID <> '0' and TblA.PropertyID LIKE '%' + @PropertyID + '%'  
       order by TblA.PropertyID asc;"
>

<SelectParameters>
    <asp:SessionParameter DefaultValue="" Name="PropertyID" SessionField="PropertyID"
        Type="string" />
</SelectParameters>
</asp:SqlDataSource>

I am programming in VB.net but this is really standalone SQL.

Answer 1

In this small of a query I wouldn't alias anything. It doesn't make it any more readable and you don't use those column aliases anywhere else in the query.

Don't do the reporting stuff on the SQL Server. you can set the title of the column in the datagrid. Don't do it here, it creates clutter if you try to do it in the SQL.

Whatever you are using to display this data is what you should use to give it a display title.

Always separate retrieving data from displaying data it makes it easier to code.

---

I assume that you have all of your RegionID's Positive so you could change the query from

WHERE RegionID <> '0'

to

WHERE RegionID > 0

I hope that RegionID is an Integer in which case the ' are not needed because they make it a string.

---

Something else that I just noticed, must have been an addition when you edited your post.

WHERE TblA.PropertyID LIKE '%' + @PropertyID + '%'

I always think of an ID field being an integer, and if that is the case then you shouldn't (and CANNOT) use Wildcards or the LIKE operator on that column. I really don't know how it is running like that.

the LIKE Operator uses a lot of performance as well, so let's say it works, why would you want to do this in the first place, if you put in 1 for @PropertyID it will bring back records with a PropertyID of

1,11,111,1111,12,13,14,15,16,17,18,19,21,31,41,51,61,71,81,91,(100 <= x < 200)... 

if you put in 13 you will get

13,113, 133,1413,1313,1334,1113,2132,1132, ...

I don't see this as being what you want to functionally happen in this query

---

You don't need this in the Query

ORDER BY TblA.PropertyID ASC

This should be done in the VB code as well.

---

This is more of a common coding practice type of thing (nitpick) all of your reserved words should be in all Caps

WHERE, JOIN, INNER, SELECT, LIKE, FROM, ORDER BY, AND, AS, ON