How to display name instead of foreign key ID?

Applications Forms
#1

Hi there,

I am trying to create a form based on a MySQL table “Assessment”. This form contains a SELECT field which is linked to a second table called “Item”.
The SELECT field is supposed to display 2 fields of the Item table --> “Itemcode” and “Itemname”.
The field Itemcode again is a foreign key on the Item table; its real name is defined in a third table called “Code” (field “Codename”).

So far, I have used the following SELECT statement:

SELECT iditem, sc_concat(code_idcode, ’ - ', name)
FROM item
ORDER BY name

Now, when using the SELECT field, it only shows the foreign key ID of the Itemcode together with the Itemname, e.g.:
" 54 - Surface Conditioning ".

How is it possible to display the real name of Itemcode (contained in third table “Code”) instead of the foreign key ID? The correct result is supposed to look like this:

" Df4 - Surface Conditioning "
(–> Df4 is stored in table “Code”, field “Codename”).

Thank you in advance and kind regards
Gustav

#2

Re: How to display name instead of foreign key ID?

If you can to show us the tables structure and its relations, maybe we can help you, but reading your post you can try that:

SELECT iditem, sc_concat(Codename, ’ - ', name)
FROM item, Code
WHERE iditem = Itemcode
ORDER BY name

Regards

#3

Re: How to display name instead of foreign key ID?

Thank you. Meanwhile, I used the JOIN command to connect the tables. It was also working fine. The system shows the regular names instead of their IDs now.

Thanks again.