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
Mysql question
Re: Mysql question
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
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.
Re: Mysql question
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
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
Re: Mysql question
Hi fciprian,
did you try what pboetcher suggested?
Olaf
did you try what pboetcher suggested?
Olaf
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: Mysql question
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!
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!
Re: Mysql question
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: SQL (from pboetcher) for result shown in the image above:
SQL for tables:
You will need to give more information about your database. A screenshot of the relevant tables (in AG, left side) is probably helpful.
Olaf
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: 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
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;
Olaf
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: Mysql question
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?
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.
Re: Mysql question
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!
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!
Re: Mysql question
Hi,
use concat(Name,' ', Surname) as Name in the sql statement
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.
Re: Mysql question
Hi
attention: Concat will fail if one of the columns is NULL.
Maybe (code not tested):
SQL can be nicer to have no space before / after - but google for that or try:
Olaf
attention: Concat will fail if one of the columns is NULL.
Maybe (code not tested):
Code: Select all
concat ((IfNull(Name, ''),' ',(IfNull(Surname, ''))
Code: Select all
trim(concat ((IfNull(Name, ''),' ',(IfNull(Surname, '')))
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view