Merge results and cross with another table

Question

Merge results and cross with another table

Navigation

#1 by (1 votes)
#2 by (-1 votes)

1

I have two tables in MySQL:

table_agendamentos:

id - tratamentos
1  - 1
2  - 1,2,3
3  - 2,3

table_tratamentos:

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

mysql

asked by anonymous 13.10.2014 / 20:14

2 answers

How to collect iPhone Internet usage information with Xamarin / monotouch Connection Error with the Database in ANDROID

score 1 · Answer 1

Assuming you can normalize table_agendamentos according to the comments, you can get the result you want with a group_concat .

Standard scheduling table:

SELECTa.id_agendamento,GROUP_CONCAT(t.id),GROUP_CONCAT(t.nome)FROMtable_agendamentosaINNERJOINtable_tratamentostONt.id=a.id_tratamentosGROUPBYa.id_agendamento;

Result:

Example in sqlfiddle

score -1 · Answer 2

Here's a chance:

Create a table and separate the ids and possible treatments with separate records.

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 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;