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

Groups > comp.databases.ms-sqlserver > #1732 > unrolled thread

How could I add ProductName in this select query

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#1732 — How could I add ProductName in this select query

I have this relation. The BookingDetails is a connection table because we 
have many to many between Booking and Products table
One Customers can have many bookings
Booking  (1)----------(m)BookingDetails 
(m)-----------------------(1)Products
   (m)
    |
    |
    |
  (1) Customers

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";

//Tony 

[toc] | [next] | [standalone]

#1733

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

[toc] | [prev] | [next] | [standalone]

#1735

Where is the ProductName in the select that you wrote ?

//Tony

"Erland Sommarskog" <esquel@sommarskog.se> skrev i meddelandet 
news:XnsA2F1B216F1D6AYazorman@127.0.0.1...
> 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 

[toc] | [prev] | [next] | [standalone]

#1736

It work now
many thanks!

//Tony
"Tony Johansson" <johansson.andersson@telia.com> skrev i meddelandet 
news:lg20c0$9jp$1@dont-email.me...
> Where is the ProductName in the select that you wrote ?
>
> //Tony
>
> "Erland Sommarskog" <esquel@sommarskog.se> skrev i meddelandet 
> news:XnsA2F1B216F1D6AYazorman@127.0.0.1...
>> 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
> 

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web