AppGini Community Forums

Posted: **2020-04-20 11:33**

Hello

Sorry if this is a stupid question but i am new to mysql useâ€¦ with this times with covid , i try to help the clinic workflow where i work but i am stuck on a copy thing from data tablesâ€¦.

So, i have 2 tabels A and B.

A has 3 fields: id , name , code. (where code is a number between 1 and 30 (code 1 - irm ; code 2 - ct ; code 3 - ecography)
id name code
1 IRM 1
2 CT 2
3 Ecography 3
etc

B has 3 fields: id, name , investigation (where name is the same as A fields name and investigation SHOULD be the name from the A name field (but acording to the code)

id name investigation_code
1 Arthur M. 2
2 Athen A. 1
3 Martha A. 3
4 Marin V. 3
etc

I need to get the name from the TABLE A (where CODE has a NAME) and WRITE in the B table in investigation with the NAME (replace the CODE with the TEXT). So where is code 1 to write to table B field investigation â€œIRM" , where is code 2 to write table B on investigation field â€œCTâ€ etc.

I need this results (in the B table to WRITE in the db the text name of investigation in sted of investiogation CODE)

id name investigation
1 Arthur M. CT
2 Athen A. IRM
3 Martha A. Ecography
4 Marin V. Ecography
etc

Please help me, i apriciate.

Have a nice day!

Dr. Filipiuc Ciprian

Posted: **2020-04-20 17:56**

Hi,

not sure what you mean by WRITE in table B as table contains already investigation code.

To get the data you can use

SELECT b.id, b.name, a.name from b left join a on a.code=b.investigation_code

Posted: **2020-04-21 00:33**

I need a table without codes. I need a table with name and investigation (as text) and ID for the table.
I just need to extract the investigation name and replace it on investigation code.

Or i need the sql query to "calculate the value" in sted of code to show me investigation name.

tryed:

SELECT `A`.`name` FROM `A`
WHERE `B`.`investigation_code`='A`.`code`

but no luck...

Thank you

Posted: **2020-04-21 06:04**

Hi fciprian,

did you try what pboetcher suggested?
Olaf

Posted: **2020-04-21 08:46**

Hello sir!
Yes, i have tryed. The thing is from 1380 rows he i got only 1120 rows. Maybe becouse some fileds are empty and skips them?

There is no options just TO REPLACE the numeric code with the TEXT name according to the A table (an code represents a name)?

Thank you!

Posted: **2020-04-21 09:50**

Hi,

that's what the SQL does: It combines the name from one table with the code from the other.
Maybe you have doubles in your table B?
As data is saved in a "normalized" fashion, it's just right, that in table B only the code (number) is saved.
What is sometimes missing?
In table A all values should have in all fields (id , name , code).
We do not know much about your setting, but IF a person occurs in table B that record should also have an entry in every field (id, name, investigation_code).

Sorry, but the SQL from pboetcher seems correct to me. I created table a and b with your data.
I also added a new value to table A that is not used in table B.
I also added a new value to table B that has no entry in column investigation_code.

The SQL returns the correct:

: b.png (8.08 KiB) Viewed 3777 times

SQL (from pboetcher) for result shown in the image above:

Code: Select all

SELECT table_b.id, table_b.name, table_a.name from table_b left join table_a on table_a.code=table_b.investigation_code

SQL for tables:

Code: Select all

-- phpMyAdmin SQL Dump
-- version 4.9.2
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Erstellungszeit: 21. Apr 2020 um 10:49
-- Server-Version: 8.0.18
-- PHP-Version: 7.4.0

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

--
-- Datenbank: `test`
--

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃ¼r Tabelle `table_a`
--

CREATE TABLE `table_a` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `code` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Daten fÃ¼r Tabelle `table_a`
--

INSERT INTO `table_a` (`id`, `name`, `code`) VALUES
(1, 'IRM', 1),
(2, 'CT', 2),
(3, 'Ecography', 3),
(5, 'somethingelse', 5);

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃ¼r Tabelle `table_b`
--

CREATE TABLE `table_b` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `investigation_code` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Daten fÃ¼r Tabelle `table_b`
--

INSERT INTO `table_b` (`id`, `name`, `investigation_code`) VALUES
(1, 'Arthur M.', 2),
(2, 'Athen A.', 3),
(3, 'Martha A.', 3),
(4, 'Marin V.', 4),
(6, 'someone else', NULL);

--
-- Indizes der exportierten Tabellen
--

--
-- Indizes fÃ¼r die Tabelle `table_a`
--
ALTER TABLE `table_a`
  ADD PRIMARY KEY (`id`);

--
-- Indizes fÃ¼r die Tabelle `table_b`
--
ALTER TABLE `table_b`
  ADD PRIMARY KEY (`id`);
COMMIT;

You will need to give more information about your database. A screenshot of the relevant tables (in AG, left side) is probably helpful.

Olaf

Posted: **2020-04-21 18:02**

Hi,

if you have empty entries in the b table, what would you expect to get as a result?
I still do not get what exactly you try to do. Has this to do with AppGini, or is it a general SQL question?

Posted: **2020-04-22 12:27**

Hello.
Yes , i saw now that i have doubles in table B!
Code worked now!!

THANK YOU VERRY MUCH!

You guys made my day

I have one little question :

How can i get from coloum 1 and coloum 2 (name and surname) to be in one cloumn
Ex:
ID Name Surname
1. Ciprian Filipiuc
2. Andrei Marinescu
etc

I need to have id. and name (name contains name and surname)
ID Name
1. Ciprian Filipiuc
2. Andrei Marinescu
etc

Thank you verry much!

Posted: **2020-04-22 18:28**

Hi,

use concat(Name,' ', Surname) as Name in the sql statement

Posted: **2020-04-23 09:39**

Hi

attention: Concat will fail if one of the columns is NULL.
Maybe (code not tested):

Code: Select all

concat ((IfNull(Name, ''),' ',(IfNull(Surname, ''))

SQL can be nicer to have no space before / after - but google for that or try:

Code: Select all

trim(concat ((IfNull(Name, ''),' ',(IfNull(Surname, '')))

Olaf

AppGini Community Forums

Mysql question

Mysql question

Re: Mysql question

Re: Mysql question

Re: Mysql question

Re: Mysql question

Re: Mysql question

Re: Mysql question

Re: Mysql question

Re: Mysql question

Re: Mysql question