Forum > Databases
String representation instead of a foreign key.
@VladislavL:
Hi everyone. I'm a newbie, so don't beat me too hard, please.
I followed MasterDetail example from https://wiki.freepascal.org/MasterDetail#Countries_and_cities_example. Example consist of two tables: "countries" - master and "cities" - details.
Cities contains foreign key "country_id" and TDBGrid of "cities" displays foreign key. I'd like to know how to display textual representation of country - I mean how to show country_name instead of country_id and don't lose foreign key linked with TDBGrid cell, at the same time? I suppose TDBGrid cell must contain foreign key and textual representation simultaneously.
paweld:
You don't need to change the table and key structures. In SQL, the SELECT statement is used to get data, and you need to modify it just for the details(TSQLQuery), instead of:
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT *FROM citiesWHERE cities.COUNTRY_ID = :COUNTRY_ID
set:
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT m.CITY_ID, m.CITY_NAME, k.COUNTRY_NAMEFROM cities m INNER JOIN countries k ON (m.COUNTRY_ID=k.COUNTRY_ID)WHERE m.COUNTRY_ID = :COUNTRY_ID
@VladislavL:
--- Quote from: paweld on February 19, 2023, 07:56:11 am ---You don't need to change the table and key structures. In SQL, the SELECT statement is used to get data, and you need to modify it just for the details(TSQLQuery), instead of:
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT *FROM citiesWHERE cities.COUNTRY_ID = :COUNTRY_ID
set:
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT m.CITY_ID, m.CITY_NAME, k.COUNTRY_NAMEFROM cities m INNER JOIN countries k ON (m.COUNTRY_ID=k.COUNTRY_ID)WHERE m.COUNTRY_ID = :COUNTRY_ID
--- End quote ---
Thank you for reply, but it is not a solution. Your solution just show textual representation from related table, but foreign key was lost. For example if I need to implement CRUD functionality, I need foreign key too. User should see text, but I need foreign key for INSERT operation for example.
paweld:
change the query as follows:
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT m.CITY_ID, m.CITY_NAME, m.COUNTRY_ID, k.COUNTRY_NAMEFROM cities m INNER JOIN countries k ON (m.COUNTRY_ID=k.COUNTRY_ID)WHERE m.COUNTRY_ID = :COUNTRY_ID
you can also get the COUNTRY_ID from the master query
@VladislavL:
--- Quote from: paweld on February 19, 2023, 02:11:39 pm ---change the query as follows:
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT m.CITY_ID, m.CITY_NAME, m.COUNTRY_ID, k.COUNTRY_NAMEFROM cities m INNER JOIN countries k ON (m.COUNTRY_ID=k.COUNTRY_ID)WHERE m.COUNTRY_ID = :COUNTRY_ID
you can also get the COUNTRY_ID from the master query
--- End quote ---
Thank you, but I need to be more clear. My problem is not with SQL and retrieving data from a database. My problem is with TDBGrid. Let me simplify the question. Let's remove master table from UI and keep only cities. When a user added a new row to cities, he should choose country, from combobox or from custom dialog, it's not matter. country_name I need to display for a user in a list of countries, I cannot allow user to choose country by country_id, so I need country_name. But when I insert a new row to database table (in INSERT statement - insert into cities(city_id, city_name, country_id) values...), I need country_id, not country_name. My question is: how to do this? It seems to me every cell in country TDBGrid-column should know not only country_name but also country_id (foreign key for countries).
Navigation
[0] Message Index
[#] Next page