Join error of two tables. By doing a group by and ordering order by timestamp date

Question

Join error of two tables. By doing a group by and ordering order by timestamp date

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

1

I need to know why you answer me as I need a query, I have a table of medical data and another of patients where two strings called id are not referenced or anything.

my medicals table.

+--------------------------------------+---------+---------------------+---------------+
| id                                   | control | date                | timestamp     |
+--------------------------------------+---------+---------------------+---------------+
| 64f5fac0-96e8-11e6-938b-25cb5cd69242 | 2       | 2017-02-21 18:17:22 | 1487719042879 |
| 64f5fac0-96e8-11e6-938b-25cb5cd69242 | 1       | 2017-02-21 18:16:13 | 1487718961328 |
| 64f5fac0-96e8-11e6-938b-25cb5cd69242 | 0       | 2017-02-20 13:24:44 | 1487615084580 |
+--------------------------------------+---------+---------------------+---------------+

my patients table.

+--------------------------+--------------------------------------+---------------+-----------------+
| _id                      | id                                   | nombres       | numeroDocumento |
+--------------------------+--------------------------------------+---------------+-----------------+
| 5808fb0331a4dd0917e0abf1 | 64f5fac0-96e8-11e6-938b-25cb5cd69242 | jose geronimo | 17322147        |
+--------------------------+--------------------------------------+---------------+-----------------+

when I define a select

SELECT 
   id, control, date, timestamp
FROM
    medicals
WHERE
    id = '64f5fac0-96e8-11e6-938b-25cb5cd69242'
 Group BY id
 ORDER BY date DESC;

I have this as a result

+--------------------------------------+---------+---------------------+---------------+
| id                                   | control | date                | timestamp     |
+--------------------------------------+---------+---------------------+---------------+
| 64f5fac0-96e8-11e6-938b-25cb5cd69242 | 1       | 2017-02-21 18:16:13 | 1487718961328 |
+--------------------------------------+---------+---------------------+---------------+

but I really need control (2)

+--------------------------------------+---------+---------------------+---------------+
| id                                   | control | date                | timestamp     |
+--------------------------------------+---------+---------------------+---------------+
| 64f5fac0-96e8-11e6-938b-25cb5cd69242 | 2       | 2017-02-21 18:17:22 | 1487719042879 |
+--------------------------------------+---------+---------------------+---------------+

because I am grouping and ordering. but I need it in a slightly more complicated join I tried to place it.

SELECT 
   id, control, date, timestamp
FROM
    medicals
WHERE
    id = '64f5fac0-96e8-11e6-938b-25cb5cd69242'
 order by date desc limit 1;

this is the result.

+--------------------------------------+---------+---------------------+---------------+
| id                                   | control | date                | timestamp     |
+--------------------------------------+---------+---------------------+---------------+
| 64f5fac0-96e8-11e6-938b-25cb5cd69242 | 2       | 2017-02-21 18:17:22 | 1487719042879 |
+--------------------------------------+---------+---------------------+---------------+

but having this immersed in a join. just keep bringing me to that id control 1.

SELECT 
    numeroDocumento, nombres, control, medicals.id
FROM
    patients
       JOIN
    medicals ON medicals.id = patients.id
WHERE
    numeroDocumento = '17322147'
GROUP BY medicals.id
ORDER BY medicals.date DESC;

I have this as a result ..

+-----------------+---------------+---------+--------------------------------------+
| numeroDocumento | nombres       | control | id                                   |
+-----------------+---------------+---------+--------------------------------------+
| 17322147        | jose geronimo | 1       | 64f5fac0-96e8-11e6-938b-25cb5cd69242 |
+-----------------+---------------+---------+--------------------------------------+

I do not know what to do any help thanks in advance.

mysql sql

asked by jose geronimo 05.04.2017 в 23:24

source

3 answers

Show name and extension of a file when editing form using carrierwave gem in rails Get JSON subkey

score 0 · Answer 1

I'll give you an example using three tables. As you can see the query does the filter by control = 2 which is what you said you were interested.

If for some reason the proposal to use a third table does not interest you, you can adapt the Query 1 indicated below in your current design and it should work.

In case of using a third table I would do the relations through an id field of the type autonumérico instead of varchar, but that is optional.

Notes:

I think a third table is better because in your current data there are columns that are repeated in both tables.

I put the control column in the third table as an intuition. When not knowing the complete design of the database, this column could be, as originally, in the table medicals . If so, the Query 1 will work if you indicate that this column is in medicals and not in the associative table as I put it.

SQL Fiddle

MySQL 5.6 Schema Setup :

CREATE TABLE medicals
    ('medical_id' varchar (50))
;

INSERT INTO medicals
    ('medical_id')
VALUES
    ('64f5fac0-96e8-11e6-938b-25cb5cd69242')
;

CREATE TABLE patients
    ('patient_id' varchar(50), 'patient_nom' varchar(70))
;

INSERT INTO patients
    ('patient_id', 'patient_nom')
VALUES
    ('5808fb0331a4dd0917e0abf1', 'José Gerónimo')
;


CREATE TABLE patients_medical
    ('medical_id' varchar(50), 'patient_id' varchar(50), 'control' int)
;

INSERT INTO patients_medical
    ('medical_id', 'patient_id', 'control')
VALUES
    ('64f5fac0-96e8-11e6-938b-25cb5cd69242', '5808fb0331a4dd0917e0abf1', 1),
    ('64f5fac0-96e8-11e6-938b-25cb5cd69242', '5808fb0331a4dd0917e0abf1', 2),
    ('64f5fac0-96e8-11e6-938b-25cb5cd69242', '5808fb0331a4dd0917e0abf1', 3)

;

Query 1 :

SELECT
  m.medical_id, p.patient_id, p.patient_nom, pm.control
FROM
  patients_medical pm
INNER JOIN patients p ON pm.patient_id=p.patient_id
INNER JOIN medicals m ON pm.medical_id=m.medical_id
WHERE pm.control=2

Results :

|                           medical_id |               patient_id |   patient_nom | control |
|--------------------------------------|--------------------------|---------------|---------|
| 64f5fac0-96e8-11e6-938b-25cb5cd69242 | 5808fb0331a4dd0917e0abf1 | José Gerónimo |       2 |

score 0 · Answer 2

The GROUP BY switch always returns the first record and the rest is discarded ; this can not be avoided.

Solution:

Since you do not need to perform an operation on a "group of records" ( SUM(), COUNT(), etc. ) , then you can take the% modifier% co_ and use GROUP BY to sort the records by their ORDER BY date DESC and date to limit to LIMIT 1

Example:

SELECT P.id, P.numeroDocumento, P.nombres, M.control
FROM patients AS P
INNER JOIN medicals AS M
  ON M.id = M.id
WHERE P.numeroDocumento = '17322147'
ORDER BY M.date DESC
LIMIT 1;

Demo

score 0 · Answer 3

Thanks to all I found an answer applying set theory I made a query that groups what I need that is my pivot table and then in two join I get the medical and patient data.

 SELECT medicals.*, patients.*
    FROM (
        SELECT p._id AS p_id, (
            SELECT
                 medicals._id
            FROM patients
                INNER JOIN
                    medicals ON medicals.id = patients.id
            WHERE
                numeroDocumento = p.numeroDocumento
            GROUP BY control
            ORDER BY medicals.date DESC
            LIMIT 1
        ) AS medical_id
        FROM patients AS p
    ) AS pivot
     INNer JOIN patients ON pivot.p_id = patients._id
     INNer JOIN medicals ON pivot.medical_id = medicals._id