How to display data from multiple tables?

Question

I have two different tables:

tbl_cinema

cinema_id
cinema_name
cinema_address
cinema_phone
cinema_email

and

tbl_cinema_level

cinema_level_id
cinema_level_name
cinema_id

How can I display the cinema_name and cinema_level_name?

My method for displaying the data:

public void displayCinema() 
{
    Utility ut = new Utility();
    SqlConnection con = ut.openconnection();
    string query = "select tbl_cinema.cinema_name, tbl_cinema_level.cinema_level_name from tbl_cinema LEFT OUTER JOIN tbl_cinema_level on tbl_cinema.cinema_id=tbl_cinema_level.cinema_id group by tbl_cinema.cinema_name, tbl_cinema_level.cinema_level_name;";
    SqlCommand cmd = new SqlCommand(query,con);
    SqlDataReader dr = cmd.ExecuteReader();
    StringBuilder sb = new StringBuilder();
    while (dr.Read())
    {
        sb.Append("<ul><li><h3>" + dr["cinema_name"].ToString() + "</h3>
                   <ul><li>" + dr["cinema_level_name"].ToString() + "</li></ul></li>");
    }
    Literal1.Text = sb.ToString();
}

All the cinema levels should be listed under the cinema name that it belongs.

Edit:

 <div id="display_cinema">
        <asp:Literal ID="Literal1" runat="server"></asp:Literal>
 </div>

I want to list it as ,,

cinema name 1
cinema level name 1
cinema level name 2

cinema name 2
cinema level name 1
cinema level name 2

Instead i am getting output as follows..

cinema name 1
cinema level name 1

cinema name 1
cinema level name 2 and so on..

Each iteration through the datareader will create a new unordered list. — Matt M
– Matt M, Commented Feb 28, 2014 at 17:58

Vignesh Kumar A · Accepted Answer · 2014-02-28 16:22:48Z

1

Try this

SELECT C.cinema_name,CL.cinema_level_name
FROM tbl_cinema C JOIN tbl_cinema_level ON
C.cinema_id = CL.cinema_id

You have to use like

 string query = "SELECT C.cinema_name,CL.cinema_level_name FROM tbl_cinema C JOIN tbl_cinema_level ON C.cinema_id = CL.cinema_id"

JOIN in SQL Server

In SQL joins are used to get data from two or more tables based on relationship between some of the columns in tables. In most of the cases we will use primary key of first table and foreign key of secondary table to get data from tables by using this relationship we can reduce the duplication of data in every table.

answered Feb 28, 2014 at 16:22

Vignesh Kumar A

28.6k14 gold badges67 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Homer Kamal Over a year ago

thx for the reply. i do know about using joins but i have been having problem while listing them by binding in html code as shown above.

Wasif Hossain · Accepted Answer · 2014-02-28 17:08:35Z

1

I think you have some problem while you are appending the formatted html tags to StringBuilder below:

sb.Append("<ul><li><h3>" + dr["cinema_name"].ToString() + "</h3>
               <ul><li>dr["cinema_level_name"].ToString()</li></ul></li>");
                       ^                                 ^              ^
                       |                                 |              |

Try to replace with the following statement:

sb.Append("<ul><li><h3>" + dr["cinema_name"].ToString() + "</h3>" + 
              "<ul><li>" + dr["cinema_level_name"].ToString() + "</li></ul></li></ul>");

answered Feb 28, 2014 at 17:08

Wasif Hossain

3,9501 gold badge20 silver badges20 bronze badges

Collectives™ on Stack Overflow

How to display data from multiple tables?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related