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



Home Answers Viewqa SQL Bridge table in SQL

Have Programming Question? Ask it here!
 
 
 


Sarbu Diana
Bridge table in SQL
3 Answer(s)      2 years 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
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 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
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
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
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
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
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
Sql
Sql  how to find maximum of a101,a102,a103 from a table in sql
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
Connection using Jdbc-odbc bridge Driver
Connection using JDBC-ODBC bridge driver JDBCExample.java...;Getting All Rows from a table!");   ... bridge driver create connection between java application and 'MSAccess
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... table in SQL is given as: CREATE VIEW Stu_View( Stu_id
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
SQL AND Operators
SQL AND Operators      ... with Example The Tutorial illustrates an example from SQL AND Operator. In this Tutorial, Table Stu_Table is created using create table statement. The insert
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
SWT_AWT bridge ,jtextfield edit problem - Swing AWT
SWT_AWT bridge ,jtextfield edit problem  Hi All, I am using SWT_AWT bridge in my code to embed swing components in to SWT Composite. I am using SWT_AWT bridge frame and adding JTextfield to it, My problem is that the JTextfield
table
table  multiplicatyion 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
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... from 'SQL Alias'. To grasp this example, we create a table 'Stu_Table
Table
Table  How i generate table in showMessageDialog. I want that i creat a table and run in showMessageDialogeprint("cprint("code sample");ode sample
SQL
SQL Trigger query  Why we use the Trigger and what it's uses?  ... in response to certain events on a particular table in a database. Triggers can...)Provide transparent event logging 5)Provide auditing 6)Maintain synchronous table
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
difference between sql and oracle  what is the difference between sql and oracle
sql
sql  how to get first row in sql with using where condition in sql? how to get last row in sql with using where condition in sql
Advertisements
 

 

 

DMCA.com