• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Jack Bush
Ranch Hand
Posts: 235
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 553
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jack Bush
Ranch Hand
Posts: 235
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 235
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic