This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes Object Relational Mapping and the fly likes How to carry out SQL query on Composite key OneToMany relationship tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "How to carry out SQL query on Composite key OneToMany relationship tables" Watch "How to carry out SQL query on Composite key OneToMany relationship tables" New topic
Author

How to carry out SQL query on Composite key OneToMany relationship tables

Jack Bush
Ranch Hand

Joined: Oct 20, 2006
Posts: 235
Hi All,

I need you help with a MySQL query to search for all the phone numbers of an employee, where these tables are linked by a jointable as follows:



Unfortunately, no result was found. I am new to SQL in general and some guidances.

These tables have been populated by a JPA application.

Your assistance would be appreciated.

Thanks,

Jack
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
The SQL is unusual, but should work. Try removing each case until you get a result. Also ensure you actually have the data in the tables that you think you do (did you commit the transaction).

In general a primary key of FIRSTNAME, SURNAME, SEX, DOB ADDRESS, CITY, is a very bad idea, as all of these are mutable fields. You should add a generated id field.

TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
Jack Bush
Ranch Hand

Joined: Oct 20, 2006
Posts: 235
Hi James,

Thanks for reminder to ensure that the data is in the PHONE table which didn't turned out to be the case. I am still debugging the JPA program to identify where the root cause is, and will try out the same query once the data is populated.

Thanks again,

Jack
Jack Bush
Ranch Hand

Joined: Oct 20, 2006
Posts: 235
Hi James,

The original SQL now works after having corrected the JPA and populated PHONE table. I understand your concern wrt the mutable fields, they are only a hypothetical example thought of at the time. The actual table that I am working on are PROPERTY and PRICE with the following SQL:

However, I wonder whether it is possible to optimise it using various JOINS or subqueries? Btw, how would adding generated id field makes these records more immutable? I didn't know that it is possible to using a combination of composite key / IDClass together with a generated id, certainly not auto generated id?

Thanks,

Jack
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: How to carry out SQL query on Composite key OneToMany relationship tables
 
Similar Threads
How to avoid duplicate inserts in JPA HQL (EJB 3.0 - Java EE 5)
SQL Exception:General Error
How can I map beans with static builder classes in CASTOR
How to ignore / merge duplicates in OneToMany when using multiple emf (ProJPA2)
Comparable and Comparator