Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1733

Re: How could I add ProductName in this select query

Show all headers | View raw

Tony Johansson (johansson.andersson@telia.com) writes:
> The primary key in Products is ProductID In this select clause I want to
> add in the result the ProductName that exist in the Product table. 
> How could I do that ?
> 
> Command.CommandText = "SELECT BokningFromDate, BokningToDate, Name, 
> Address, 
> Phone, ProductID, EnhetsPris " +
>           "FROM Bokningar " +
>           "INNER JOIN Customers " +
>           "ON Bokningar.CustomerID = Customers.CustomerID " +
>           "INNER JOIN BokningDetails " +
>           "ON Bokningar.BokningarID = BokningDetails.BokningarID " +
>           "ORDER BY Name asc";
> 

 Command.CommandText = 
    @"SELECT BokningFromDate, BokningToDate, Name, Address, 
             Phone, ProductID, EnhetsPris, P.ProductID
      FROM   Bokningar B
      JOIN   Customers C ON B.CustomerID = C.CustomerID
      JOIN   BokningDetails BD ON B.BokningarID = BD.BokningarID 
      JOIN   Products P ON BD.ProudctID = P.ProductID
      ORDER BY Name asc";

Assumining that the language is C#, by using the @ prefix for a string 
literal, it can spill over many lines. This increases the readability
of the SQL. I have also introduced aliases for the tables, also to 
improve readability. Repeating the table names again and again, makes
it difficult to see the forest for the trees. 

You should have prefixes for all columns in a multi-table query. This
makes the query easier to read for anyone who don't know the tables
by heart. It also prevents the query from breaking if a column with 
the same name is added to another table in the query.


-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

How could I add ProductName in this select query "Tony Johansson" <johansson.andersson@telia.com> - 2014-03-15 17:05 +0100
  Re: How could I add ProductName in this select query Erland Sommarskog <esquel@sommarskog.se> - 2014-03-15 17:30 +0100
    Re: How could I add ProductName in this select query "Tony Johansson" <johansson.andersson@telia.com> - 2014-03-15 17:50 +0100
      Re: How could I add ProductName in this select query "Tony Johansson" <johansson.andersson@telia.com> - 2014-03-15 18:09 +0100

csiph-web