Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



Home Answers Viewqa SQL Bridge table in SQL
Login         

View Questions and Answers by Category

Advertisements


 
Have Programming Question? Ask it here!
 
 
 


Sarbu Diana
Bridge table in SQL
3 Answer(s)      2 years and 3 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


Advertisement
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.



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
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
The JDBC-ODBC Bridge.
The JDBC-ODBC Bridge.  Are there any ODBC drivers that do not work with the JDBC-ODBC Bridge
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
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
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
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
JDBC-ODBC Bridge multi-threaded.
JDBC-ODBC Bridge multi-threaded.  Is the JDBC-ODBC Bridge multi-threaded
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 <
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
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
Weigh Bridge integration using java
Weigh Bridge integration using java  Hi!! every one, my name is srihari, i am a java developer. now i need a solution for my project. How can i... from the weighing bridge, and save the data in database using java, after that i
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
FOP Bridge
FOP Bridge       The FOP bridge plugin provides eclipse users the ability to convert... a log of FOP's progress. FOP Bridge can also integrate rendering
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
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
Php Sql Table
Php Sql Table  This example illustrates how to create table in php. In this example we create a table "emp_table" with three fields, "... we fetched all rows and columns by select query.   Table: emp_table
Create table and insert data by sql query
Create table and insert data by sql query... to connect java application and execute sql query like create table in mysql database, insert some values and retrieve values from the table. Before running
Advertisements
 

 

 

DMCA.com