mySQL. Nested query on a table

Question

mySQL. Nested query on a table

Navigation

#1 by (0 votes)

1

I bring you a question I have about a query that brings me headlong. I have a single table that is inherited from a previous project.

In the table among many other data, I have a DNI, a certificate date (FCert) and a renewal date (FRev). In this table a record is stored every time someone goes and a certificate is made. So it has many people repeated and does not have a unique ID.

I am trying to make a query to return to me the people who got a certificate in 2006 (YEAR (FCert) = 2006) and returned to become one in 2016 (YEAR (FRev) = 2016). That is, there must be two DNI records in the table, one with each data.

I have attached the query as I do now, but it returns rare and duplicate values.

SELECT a.'DNI', a.'tabla', a.'FRev', a.'FCert', a.'FNac' FROM 'tabla' AS a JOIN tabla AS b ON (a.DNI = b.DNI) 
WHERE YEAR(a.'FRev') = 2016 AND YEAR(b.'FCert') = 2016 
ORDER BY a.'DNI' DESC

If you can throw me a cable, I'm stuck there.

mysql subconsultas

asked by user72317 15.01.2018 в 19:02

source

1 answer

Help with FileSystemWatcher and FileCopy Fontawesome in polymer 2

score 0 · Answer 1

I think you're drowning in a glass of water.

If I understand correctly, you want the data of the people who have removed the document in the year 2006 and have renewed it in the year 2016 .

So, you do not need to do JOIN of the table on itself, but apply two criteria with OR for each respective field.

HERE YOU CAN SEE COMPLETE DEMO

For example:

SELECT * 
FROM tabla_unica_20180115 
WHERE YEAR(f_cert)=2006 OR YEAR(f_rev)=2016 
ORDER BY dni DESC;

Result:

id  dni     f_cert                  f_rev
1   100     15.01.2006 00:00:00     NULL
5   100     NULL                    16.01.2016 00:00:00
4   92      19.01.2006 00:00:00     NULL
7   92      NULL                    20.01.2016 00:00:00
2   80      16.01.2006 00:00:00     NULL
6   80      NULL                    17.01.2016 00:00:00

And if you want to group, so that it shows you only the individual people who have done it, without duplicate rows:

SELECT dni
FROM tabla_unica_20180115 
WHERE YEAR(f_cert)=2006 OR YEAR(f_rev)=2016 
GROUP BY dni 
ORDER BY dni DESC;

Result:

dni
100
92
80