Mysql question

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
fciprian
Veteran Member
Posts: 52
Joined: 2020-04-20 10:51

Mysql question

Post by fciprian » 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

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Mysql question

Post by pbottcher » 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
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

fciprian
Veteran Member
Posts: 52
Joined: 2020-04-20 10:51

Re: Mysql question

Post by fciprian » 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


fciprian
Veteran Member
Posts: 52
Joined: 2020-04-20 10:51

Re: Mysql question

Post by fciprian » 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!

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Mysql question

Post by onoehring » 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
b.png (8.08 KiB) Viewed 2723 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

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Mysql question

Post by pbottcher » 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?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

fciprian
Veteran Member
Posts: 52
Joined: 2020-04-20 10:51

Re: Mysql question

Post by fciprian » 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 :x

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!

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Mysql question

Post by pbottcher » 2020-04-22 18:28

Hi,

use concat(Name,' ', Surname) as Name in the sql statement
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Mysql question

Post by onoehring » 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

Post Reply