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: 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

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
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      ... SQL Backup Table. In this Example, we create a table 'Stu_Table'. The create table statement create a table 'Stu_Table'.  Create Table Stu
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_TableADS_TO_REPLACE_1 SQL
SQL Alter Table Syntax
SQL Alter Table Syntax       SQL Alter Table Syntax modifies the existing table definition... an example from SQL Alter Table Syntax. The list of Syntax performs the following
SQL Alter Table
SQL Alter Table       SQL Alter Table is used to modify the existing table definition... an example from 'SQL Alter Table'. To understand and grasp the example we create
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.ADS_TO_REPLACE_1 Thanks
ModuleNotFoundError: No module named 'sql-xml-table'
ModuleNotFoundError: No module named 'sql-xml-table'  Hi, My... 'sql-xml-table' How to remove the ModuleNotFoundError: No module named 'sql-xml-table' error? Thanks   Hi, In your python
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 that describes how to delete a particular row from the database table using EJB. The steps
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... covers an example on 'PHP SQL Table'. To grasp the example we create a sql_table.php
SQL Alter Table Add Multiple Columns
SQL Alter Table Add Multiple Columns       Alter Table Add Multiple Columns in SQL... demonstrate Alter Table Add Multiple Columns. The SQL Query create a table 'Stu
SQL Alter Table Add Multiple Columns
SQL Alter Table Add Multiple Columns       Alter Table Add Multiple Columns in SQL... demonstrate Alter Table Add Multiple Columns. The SQL Query create a table 'Stu
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_TableADS_TO_REPLACE_1 SQL
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
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... successfully : See sql table to verify delete from employees where First
Save Table - SQL
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, "...: emp_table  ADS_TO_REPLACE_2 Source Code of sql_table.php 
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
Sql
Sql  how to find maximum of a101,a102,a103 from a table in sql
use struts 1.0 to view sql table value on JSP page
use struts 1.0 to view sql table value on JSP page  Here i am using struts 1.0 to view my sql table values on jsp page. But the problem is when i append the value in bean then i find the last row of table is shown repetedly. Any
use struts 1.0 to view sql table value on JSP page
use struts 1.0 to view sql table value on JSP page  Here i am using struts 1.0 to view my sql table values on jsp page. But the problem is when i append the value in bean then i find the last row of table is shown repetedly. Any
How to import data from sql server table into an excel file by creating the rows dynamically in the excel according to the dataabase??
How to import data from sql server table into an excel file by creating... data from sql server table into an excel file by creating the rows dynamically in the excel according to the dataabase?? There is a table in sql server having
SQL Create View
; A View is a imaginary and virtual table in SQL. In SQL, a view is defined  as a virtual table outcome as a result of an SQL statement. A view... an example from SQL Create View. In this Tutorial, we create a table 'Stu_Table
how to convert the entire column field of table to dd/mm/yyyy format in microsoft sql server?
how to convert the entire column field of table to dd/mm/yyyy format in microsoft sql server?  how to convert the entire column field which is in encryted like 1263332444 of table to dd/mm/yyyy format in microsoft sql server? i
create webpage with table sql database using netbeans 6.8 with struts complete tutorial.(include username and password)
create webpage with table sql database using netbeans 6.8 with struts complete... us,employee login,user profile upload login, everything stored on sql database.i... with struts and sql DB.i need complete tutorial document.waiting for your good reply
table
table  multiplicatyion table
SQL AND Operators
SQL AND Operators      ..._TO_REPLACE_1 Understand with Example The Tutorial illustrates an example from SQL AND Operator. In this Tutorial, Table Stu_Table is created using create table
find the sql query for the output having two table names ie, homeworklinks,homework_attachments
find the sql query for the output having two table names ie, homeworklinks... links table contains two columns like : subject , dateon homework... Telugu Project 2012-07-25 sql basics data.odt 34 Telugu Project 2012-07-27 ESS
TABLE
TABLE   Why doesn't <TABLE WIDTH="100%"> use the full browser width
Table
Table  How i generate table in showMessageDialog. I want that i creat a table and run in showMessageDialogeprint("cprint("code sample");ode sample
create webpage with table sql database using netbeans 6.8 with struts complete tutorial.(include username and password) 0 Answer(s)
create webpage with table sql database using netbeans 6.8 with struts complete tutorial.(include username and password) 0 Answer(s)  create webpage with table sql database using netbeans 6.8 with struts complete tutorial.(include
SQL Alias
; SQL Alias is the another name given to the existing table and the column. The SQL Alias make the table and column name easy to read... The given below SQL Query takes alias name for table 'Stu_Table
Table
Table  How I generate table in showMessageDialog. E.g 3X1=3 3X2=6 3X3=9print("code sample
Table
Table  How i generate table in showMessageDialog. I want to creat a table and run in showMessageDialoge. Pl make a table programe which run..., JOptionpane, Integer.parseInt. Please use only these above methods to make table
table
input from oracle table(my database table..) This is a very important table of my
table
table  Hi..I have a list of links which links to a table in the same page.If I click first link the table is displayed at the top, likewise if i click the last link the table is displayed at the last,i dont know how to set
Table
Table  How i generate table in JOptionpane.showMessageDialog... advance coding but i want u make the table using JOptionpane.showMessageDialog, import.javax.swing int Integer.parseInt(). Thats my limit. Pl generata a table
table?
table?  Hi, how could i make a table in javascript, which would look like this: AA CODON Number /1000 Fraction .. (this row... can't figure out, how to construct a table,with two fixed columns, one that reads
Table
Table  Why u dont understand sir?? I want to make a table program which generate a table on showMessageDialog. I have learnt these methods until now... methods to be used. Write a table program which use only and only these above methods

Ads