Bridge table in SQL

Ads

 
 

Share on Google+Share on Google+

Sarbu Diana
Bridge table in SQL
3 Answer(s)      5 years and 2 months ago
Posted in : SQL

I have 2 tables: professors(professorid INT PK, name VARCHAR), courses(courseid INT PK, title). I also have a bridge table: professorscourses(profid INT FK, course_id INT FK).

I still didn't find a query for something like this: given the professor name, "x"

Using the bridge table, find all the courses for professor X.

professors prof_id | name 1 John

professors_courses profid | courseid 1 2 1 3 1 5 2 1

courses course_id | title 1 English 2 French 3 Italian 4 Japanese 5 Polish

Given the name "John", seems he has the following courses: French,Italian,Polish

Ads
View Answers

July 27, 2012 at 11:07 AM


Here is a jsp code of dependent dropdown. The given code retrieves the professor names from the database and stored into dropdown. When the user selects any name professor, his/her courses is then displayed in another dropdown box on the same page. We have used Ajax for this.

1)professor.jsp:

<%@page import="java.sql.*"%>
 <html>
      <head>  
      <script language="javascript" type="text/javascript">  
      var xmlHttp  
      var xmlHttp
      function showState(str){
      if (typeof XMLHttpRequest != "undefined"){
      xmlHttp= new XMLHttpRequest();
      }
      else if (window.ActiveXObject){
      xmlHttp= new ActiveXObject("Microsoft.XMLHTTP");
      }
      if (xmlHttp==null){
      alert("Browser does not support XMLHTTP Request")
      return;
      } 
      var url="course.jsp";
      url +="?count=" +str;
      xmlHttp.onreadystatechange = stateChange;
      xmlHttp.open("GET", url, true);
      xmlHttp.send(null);
      }

      function stateChange(){   
      if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete"){   
      document.getElementById("course").innerHTML=xmlHttp.responseText   
      }   
      }
      </script>  
      </head>  
      <body>  
      <select name='professor' onchange="showState(this.value)">  
       <option value="none">Select</option>  
    <%
 Class.forName("com.mysql.jdbc.Driver").newInstance();  
 Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");  
 Statement stmt = con.createStatement();  
 ResultSet rs = stmt.executeQuery("Select * from professors");
 while(rs.next()){
     %>
      <option value="<%=rs.getString(1)%>"><%=rs.getString(2)%></option>  
      <%
 }
     %>
      </select>  
      <br>  
      <div id='course'>  
      <select name='course' >  
      <option value='-1'></option>  
      </select>  
      </div>  
      </body> 
      </html>

2)course.jsp:

<%@page import="java.sql.*"%>
<%
String profid=request.getParameter("count");  
 String buffer="<select name='course' ><option value='-1'>Select</option>";  
 try{
 Class.forName("com.mysql.jdbc.Driver").newInstance();  
 Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");  
 Statement stmt = con.createStatement();  
 ResultSet rs = stmt.executeQuery("Select * from courses where prof_id='"+profid+"' ");  
   while(rs.next()){
   buffer=buffer+"<option value='"+rs.getString(1)+"'>"+rs.getString(3)+"</option>";  
   }  
 buffer=buffer+"</select>";  
 response.getWriter().println(buffer); 
 }
 catch(Exception e){
     System.out.println(e);
 }
 %>

For the above code, we have created two database tables:

CREATE TABLE `professors` (                                 
           `prof_id` bigint(255) NOT NULL auto_increment,       
           `name` varchar(255) default NULL,               
           PRIMARY KEY  (`prof_id`)); 



CREATE TABLE `courses` (                                   
          `courseid` bigint(255) NOT NULL auto_increment,         
          `prof_id` int(255) default NULL,                     
          `title` varchar(255) default NULL,                     
          PRIMARY KEY  (`stateid`));


July 28, 2012 at 7:14 PM


Thank you for your time:) But well I found how now.

String sqlQuery = "SELECT courses.title FROM courses INNER JOIN professors_courses ON professors_courses.course_id = course.course_id WHERE prof_id = '"+x+"'";

I forgot to take into account that user chooses a professor name, I look for he's ID and then I use this Query.



July 28, 2012 at 7:14 PM


Thank you for your time:) But well I found how now.

String sqlQuery = "SELECT courses.title FROM courses INNER JOIN professors_courses ON professors_courses.course_id = course.course_id WHERE prof_id = '"+x+"'";

I forgot to take into account that user chooses a professor name, I look for he's ID and then I use this Query.


Ads









Related Tutorials/Questions & Answers:
Bridge table in SQL
Bridge table in SQL  I have 2 tables: professors(professorid INT PK, name VARCHAR), courses(courseid INT PK, title). I also have a bridge table... for something like this: given the professor name, "x" Using the bridge table
SQL Alter Table
SQL Alter Table  What is alter table statement in sql? Can u please show me an example of SQL Alter Table?? Thanks!   ALTER TABLE... also be used to modifiy, add or deleate a column in the existing table. SQL
Advertisements
SQL Alter Table Name
SQL Alter Table Name       SQL Alter Table Name is used to change or modify  name of the existing table. To understand how to change the name of created table in SQL
revinfo table sql
revinfo table sql  Hi, I want to create revinfo table manually in my MySQL Database. What is the create sql of revinfo table? Thanks   Hi, Here is the sql for creating the revinfo table: CREATE TABLE `revinfo
how to take a backup of a table in sql
how to take a backup of a table in sql  how to take a backup of a table in sql?   SQL BackUp Table example
SQL Backup Table
SQL Backup Table      .... Understand with Example The Tutorial illustrate a example from SQL Backup Table. In this Example, we create a table 'Stu_Table'. The create table statement
mysql table construction - SQL
mysql table construction  In MySql there is no pivot function. using a function or Stored Procedure, I want to loop through a column of data and create a another table that uses the row value as Column labels from the row
updating a table in a database - SQL
updating a table in a database  give me complete source code in java to execute the sqlquery such that (update table_name set phone no=? where cous_id=?). or in simple way give me source code to update my table in MsAccess
SQL Alter Table Name
SQL Alter Table Name       SQL Alter Table Name is used to change or modify  name of the existing table. To understand how to change the name of created table in SQL
SQL Alter Table Primary Key
SQL Alter Table Primary Key       Alter a Table Primary Key in SQL modifies the existing table and adds a primary key. Create Table Stu_Table SQL statement to create
SQL Alter Table Syntax
SQL Alter Table Syntax       SQL Alter Table Syntax modifies the existing table definition. Understand with Example The Tutorial illustrate an example from SQL
What?s the maximum size of a row in SQL table?
What?s the maximum size of a row in SQL table?  What?s the maximum size of a row in SQL table?   Hi, The maximum Row Size is 8060 Bytes in a sql table. Thanks
SQL Alter Table
SQL Alter Table       SQL Alter Table is used to modify the existing table definition... 'SQL Alter Table'. To understand and grasp the example we create a table 'Stu
Deleting a Row from SQL Table Using EJB
Deleting a Row from SQL Table Using EJB   ... to delete a row from the SQL Table. Find out the steps given below... updated successfully : See sql table to verify delete from employees where
How to read textfile and create SQL server table ?
How to read textfile and create SQL server table ?  hi sir, your site... trying to read textfile and create table in sql server but it gives error.../questions/16415498/creating-a-table-in-sql-database-by-reading-textfile-in-java
PHP SQL Table
PHP SQL Table       PHP SQL Table is used to create table in PHP. To create a table in PHP... on 'PHP SQL Table'. To grasp the example we create a sql_table.php begins with <
SQL Alter Table Add Multiple Columns
SQL Alter Table Add Multiple Columns       Alter Table Add Multiple Columns in SQL... Add Multiple Columns. The SQL Query create a table 'Stu_Table' with table
SQL Alter Table Add Multiple Columns
SQL Alter Table Add Multiple Columns       Alter Table Add Multiple Columns in SQL... Add Multiple Columns. The SQL Query create a table 'Stu_Table
JSP textbox autopopulation on basis of SQL table values
JSP textbox autopopulation on basis of SQL table values  Hi, I need to achieve the following on J2EE platform Could you please help? The following table is created in MySQL DB: Problem type Status Responsible LEGAL
SQL Alter Table Primary Key tutorial
SQL Alter Table Primary Key       Alter a Table Primary Key in SQL modifies the existing table and adds a primary key. Create Table Stu_Table SQL statement to create
How to search the table name in MS SQL Database 2005 from application
How to search the table name in MS SQL Database 2005 from application  How to search the table name in MS SQL Database 2005 from application from our helpdesk application? application might be in html
The JDBC-ODBC Bridge.
The JDBC-ODBC Bridge.  Are there any ODBC drivers that do not work with the JDBC-ODBC Bridge
Upload CSV File into Columns of sql table using servlets and jsp
Upload CSV File into Columns of sql table using servlets and jsp  Hello sir, plz give me the code to upload csv file data into respective columns of table
how to retrieve data from table with runtime values in sql?
how to retrieve data from table with runtime values in sql?  how to retrieve data from table with runtime values in sql? For example, I have table... at the following link: http://www.roseindia.net/sql/sqldate/sql-date-comparison.shtml
how to make JTable to add delete and update sql database table
how to make JTable to add delete and update sql database table  Hello all I want to know how to make JTable to act actively to add delete and update database table. i am struck ed here from long time please help me
JDBC-ODBC Bridge multi-threaded.
JDBC-ODBC Bridge multi-threaded.  Is the JDBC-ODBC Bridge multi-threaded
Deleting a Row from SQL Table Using EJB
Deleting a Row from SQL Table Using EJB... are going to delete a row from the SQL Table. Find out the steps given below... ===================================== Data updated successfully : See sql table to verify
Sql
Sql  how to find maximum of a101,a102,a103 from a table in sql
Version of batik>batik-bridge dependency
List of Version of batik>batik-bridge dependency
Version of com.braintreepayments>popup-bridge dependency
List of Version of com.braintreepayments>popup-bridge dependency