1
I have two tables in Mysql:
table_schedules:
id - tratamentos
1 - 1
2 - 1,2,3
3 - 2,3
table_treatments:
id - nome
1 - Facial
2 - Corporal
3 - Outros
I want to make a query where I can return the following result:
id - tratamentos_id - tratamentos_nomes
1 - 1 - Facial
2 - 1,2,3 - Facial, Corporal, Outros
3 - 2,3 - Corporal, Outros
1
Assuming you can normalize the table_agendamentos according to the comments, it is possible to get the result you want with a group_concat.
Normalized schedule table:
SELECT a.id_agendamento, GROUP_CONCAT(t.id), GROUP_CONCAT(t.nome)
FROM table_agendamentos a
INNER JOIN table_tratamentos t ON t.id = a.id_tratamentos
GROUP BY a.id_agendamento;
Upshot:
Example in sqlfiddle
-1
Follows a possibility:
Create a table and indicate separated by records the id and possible treatments.
CREATE TABLE `table_agendamentos` (
`id_table_agendamentos` int(11) NOT NULL,
`id_agendamento` int(11) NOT NULL,
`id_tratamentos` int(11) NOT NULL,
PRIMARY KEY (`id_table_agendamentos`)
);
INSERT INTO `table_agendamentos` VALUES
(1,1,1),(2,2,1),(3,2,2),(4,2,3),(5,3,2),(6,3,3);
Keep the table below:
CREATE TABLE `tabe_tratamentos` (
`id_tabe_tratamentos` int(11) NOT NULL AUTO_INCREMENT,
`nome` varchar(45) NOT NULL,
PRIMARY KEY (`id_tabe_tratamentos`)
);
INSERT INTO `tabe_tratamentos` VALUES (1,'Facial'),(2,'Corporal'),(3,'Outros');
Now to return what you want to do to the following SQL:
select * from tabe_tratamentos where id_tabe_tratamentos IN (
SELECT id_tratamentos FROM table_agendamentos where id_agendamento = 2)
or
Select TT.nome from tabe_tratamentos as TT
Inner join table_agendamentos as TA ON TA.id_tratamentos = TT.id_tabe_tratamentos
where TA.id_agendamento = 2;
All results:
Select TA.id_agendamento, GROUP_CONCAT(TT.nome) from tabe_tratamentos as TT
Inner join table_agendamentos as TA
ON TA.id_tratamentos = TT.id_tabe_tratamentos
GROUP BY TA.id_agendamento;
But this final query does not reach the expected result in the question
I changed the post:test like this:select * from tabe_treatments Where id_tabe_treatments IN ( SELECT id_treatments FROM table_schedules Where id_scheduling = 2)
The select remains wrong
Here what you can do is specify what kind of scheduling you want on the Where line TA.id_scheduling = 2; and refine the sql.
But in question Felipe wants all the names of treatments in a single line for all appointments.
Select TA.id_agendamento, GROUP_CONCAT(TT.id_tabe_tratamentos), GROUP_CONCAT(TT.nome) from tabe_tratamentos as TT 
Inner join table_agendamentos as TA
ON TA.id_tratamentos = TT.id_tabe_tratamentos
GROUP BY TA.id_agendamento;

;
Let’s go continue this discussão in chat.
Browser other questions tagged mysql
You are not signed in. Login or sign up in order to post.
Just by eye I recommend normalizing properly to
table_agendamentos. No problem repeating id if fields are composite keys.– gmsantos
If you don’t know what @gmsantos is talking about, check out an example at http://answall.com/a/33794/74
– bfavaretto