File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes subquery Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "subquery " Watch "subquery " New topic
Author

subquery

Steve Howerton
Ranch Hand

Joined: Oct 18, 2011
Posts: 41
Hello I am using Apache derby embedded database: I have been using a system to display
records from the database described in "DisplayInstructorAvailableNowJTable.java".
it allows the result set to create a model from a prepared statement.



I have only uncovered the tip of the iceberg concerning JTABLE. I am not able
to manipulate the date and enter row of data in other than the stmt or prepared statement.
for now. Any info on loading the JTABLE other than the stmt or the prepared statement
would be appreciated.

Please find attached: availableTable_screenshot.doc :

I have a table that displays (JTABLE) all to the record from the intr_available table.
that describes all instructors and their availability at all locations. Instructors at locations
at given point in time. "start date and start time". derived from instr_available table

I have a booking table that book displays bookings between instructors and student at locations
at given point in time. "start date and start time". derived from booking table

My new button action will need to display (JTABLE) the filtered availability for said instructor.

I would like to write the SQL to display records from instr_available "ia" that are not
found for the instructor in the booking "b" table. Instructors have availability but are
not available if they are involved in the booking table.












Why can I not have the Left table instr_available "ia" produce a list that does not allow records to
be displayed if it is found in the right table booking "b" ?












Thanks for any assistance with writing this prepared statement.

I was planning on getting the filtered data to display and then negotiate the next business rule. that will amend the latter.

When the new button action is activated it will know the instructor, student, and the location.









so the SQL will need to additionally receive the param for the student and further filter the new JTABLE data not to include

said student if is found in the booking table.

student at location
at given point in time. "start date and start time". derived from booking table : derived from booking table

NOTE: The student is not part of the instr_available table : When the data is viewed
it is also a rule that if said student is involved in a booking at the given point in time that data should be filtered out

of the new button action too.

The new statement will have to receive the third unused ps.setString(3,student)
student param :

LIKE ... but does not work either.










Thank you again for any new information.


Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

if you want to select items that are not in the list you want to use the operator "not in" rather than != think of 1 != 2 == true but what does 1 != [1,2,3,4,5,6,7,8,9] mean?
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

I am not sure if there were other questions in your post, but it will make discussion easier if you split it out to 1 question per post, so the discussions don't get all entangled and spagetti like.
Steve Howerton
Ranch Hand

Joined: Oct 18, 2011
Posts: 41
Thanks ,that is very usefull. I will have to trial and error the NOT operand. The turn around of me getting back to the internet but I will be working on it and I appreciate the help.

I would like to write the SQL to display records from instr_available "ia" that are not
found for the instructor in the booking "b" table. Instructors have availability but are
not available if they are involved in the booking table.

Steve Howerton
Ranch Hand

Joined: Oct 18, 2011
Posts: 41
Hello I am using Apache derby embedded database: I have been using a system to display
records from the database described in "DisplayInstructorAvailableNowJTable.java".
it allows the result set to create a model from a prepared statement.

The overall objective is to display a table the shows what points in time are actually availabe to
create a booking between a instructor and student.

I have a table that displays (JTABLE) all to the records from the intr_available table.
that describes all instructors and their availability at all locations at a specific points in time.
"start date and start time". derived from instr_available table

also, I have a booking table that displays bookings between instructors and student at locations
at given points in time. "start date and start time". derived from booking table

ps.setString(1, instructor);
ps.setString(2, student);

My new button action will need to display (JTABLE) of the filtered availability for said instructor and student

Instructors have availability defined in instr_available " ia,"

Instructors not available if they are involved in the booking table.

Further, students may be already booked for lessons too. (the booking 'b' table) So the "Available Now" btn should
also filter the available records further because it would be nice to look at possible dates and times
that a certain Instructor and student can be booked for a lesson. These dates should not include students that are
already booked for lessons.


I have written the SQL to display records from instr_available "ia" that are not
found for the instructor in the booking "b" table.




To further filter the rs. to display Available Now dates I would think it would be possible to somehow have
another subquery to set the stu_uid to eliminate the dates that students are already booked somewhere else.

I tried to add the script below and it actually filtered the dates the stu_uid was booked in the
booking table but the rs. duplicated each set of times several times. Like it ran twice.







Thank you again for any new information.


 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: subquery