Win a copy of Transfer Learning for Natural Language Processing (MEAP) this week in the Artificial Intelligence and Machine Learning forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Paul Clapham
  • Devaka Cooray
  • Bear Bibeault
Sheriffs:
  • Junilu Lacar
  • Knute Snortum
  • Liutauras Vilda
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Piet Souris
Bartenders:
  • salvin francis
  • Carey Brown
  • Frits Walraven

Get the id of persons with the same name

 
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Best, ranchers!

I am struggling with my sql. Let's say I have a table named "tbl_castperson". This is where I store basic information such as an actors name and charactername in my database. One thing that is bothering me is simply as this - what would the query and the approach be for finding the id of a person with the same person name as others in the forementioned table? I'm working on a JDialog that will present the information about the selected actor and in it there is a JTextField that display the id of the current selected person from the table "tbl_castperson".

What is the procedure for getting the unique id of a person that share the same name with other people in a database? I have looked in on sql-terms like "distinct" and "group by" but that didn't worked out for me. I have a hunch the answer is quite basic and that I am close but everything is at pause for the moment. Please help.

Very kind regards,
Robert!
 
Saloon Keeper
Posts: 11882
253
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What are the inputs? A name or a person ID?

Anyway, you probably want something like "SELECT id FROM tbl_castperson WHERE name = ?" and use a prepared statement to inject the input name, or you can use the LIKE operator instead of the '=' operator if you want to match parts of a name.

If you want to first search a name to match on, given a user ID, you can do something like this:
 
Marshal
Posts: 68904
275
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's a ong time since I wrote any SQL, but isn't it something like this?If you have multiple name selections, what would this do?I am assuming you are using id as a primary key.
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello, again!

I have a table that looks like this



The problem is that I wan't to allow duplicate entries. If I would only allow unique entries then my sql would look something like this



... as suggested earlier.

But I wan't to allow duplicates (person_name column) in my table tbl_castperson and then get the id from the current selected person in that table. This maybe sounds unclear but it something like that I am looking for. Thank you so much for your replies!

/Robert

 
Sheriff
Posts: 15519
263
Mac Android IntelliJ IDE Eclipse IDE Spring Debian Java Ubuntu Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If PERSON_NAME is not unique, then there's no way you can single out a particular instance without the ID. Without the specific ID, the best you can do is get a list of IDs where PERSON_NAME = whatever. If that list consists of only 1 row, then you've found your person. However, if the list contains more than 1 row, then you'll have to find some other attribute to use to single out a specific person.

Ignoring the technicalities, what exactly is the intent from the user's (who doesn't know anything about DB tables) point of view?
 
Stephan van Hulst
Saloon Keeper
Posts: 11882
253
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Robert Ingmarsson wrote:get the id from the current selected person in that table


In addition to what Junilu asked you, what does it mean for a person to be selected? Databases don't have a current selection.
 
Junilu Lacar
Sheriff
Posts: 15519
263
Mac Android IntelliJ IDE Eclipse IDE Spring Debian Java Ubuntu Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think I see what the problem is now. It seems you already have a list of names but don't have the corresponding IDs associated with those names. You want to go back and retroactively match up each name to their correct ID. Well, it's as I said before, you're going to need some other attribute (CHARACTER_NAME, for example) that you can combine with PERSON_NAME to uniquely identify a specific person, otherwise there's no way you can just get the ID unless there's only one row in the table with that particular PERSON_NAME.

What you should have is a separation of Model and View. You can display or hide information on your View (the user interface) but your Model (the objects that are wholly or partially displayed on the View) should have all the information you need to match up to specific tables/rows in your database. If I'm reading between the lines correctly, it seems you've skipped the Model part and have directly populated your View with data from the database. In doing so, you've lost the information (the ID) that allows you to go the other way, from View back to database. So you're like Hansel and Gretel who've had their breadcrumbs eaten by the birds and now you can't find your way back home.
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Best, Mr. Lacar!

Sorry for a late reply. You have fully understood my problem. I have a JTable in a JDialog with person_name and character_name as column identifiers. Above the JTable I have a set of JButtons. Among theese buttons there is an "edit person" button. When the user clicks this button another JDialog opens up displaying a form that asks for further information about the selected person. This is why it is so important for me to get the id of the current selected person even if he/she has duplicate entries in the person_name/character_name - JTable. You mentioned the character_name in your latest reply. Could you elaborate? What would the solution be if you put further attention to the character_name-column? Do you have any ideas, sir?

I am trying to use a MVC pattern in my design. But that is my weakest side. I am not so sure about the model part in that procedure.

Best wishes,
Robert!
 
Marshal
Posts: 25436
65
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You clearly need the ID of a person which you are working with. Maybe you don't need to display the ID in the view (that JTable) but that doesn't prevent you from storing the ID in the model. So that's what you should do. Your table model should consist of a list of Person objects, and a Person object should include the ID of the person.

So you need to fix your design to be like that.
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Best, Mr. Clapham!

I think I have solved this one out thanks to your reply. I added a CastPerson object as a value in the person_name column of my DefaultTableModel. Here is the method I used for this, it's pretty obvious what is going on in it.



And then i get the id of the selected Person with the following code



Many thanks for the reply.

Wishes,
Robert!
 
I'm just a poor boy, I need no sympathy, because I'm easy come, easy go, little high, little low, little ad
Two software engineers solve most of the world's problems in one K&R sized book
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic