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.

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

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

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

Bridge table in SQL

Questions by Category