Error with a mySql query

Question

Error with a mySql query

Navigation

#1 by (0 votes)

0

I am trying to make a query with mySql but the result is not as expected as rows are repeated and some data have no relation at all.

I need to select from the table list_task the NAME and the PUBLIC_NUMBER and the table list_project the NAME and the PUBLIC_NUMBER that refers to a user X.

So, each project ( list_project ) has zero or many columns ( list_column ) and each column has zero or many tasks ( list_task ). A user can create projects ( ADMIN_USER ), columns ( USER_ID ) and tasks ( USER_ID ). The list_myprojects table stores all the projects that a user has access to, while the list_projects table stores only the project information.

The query you created was this:

SELECT list_task.PUBLIC_NUMBER AS TASK_NUMBER, list_task.NAME AS TASK_NAME, list_project.NAME AS PROJECT_NAME, list_project.PUBLIC_NUMBER AS PROJECT_NUMBER
FROM list_task 
JOIN list_myprojects ON list_myprojects.USER_ID = list_task.USER_ID 
JOIN list_project ON list_myprojects.PUBLIC_NUMBER = list_project.PUBLIC_NUMBER 
WHERE list_task.COLUMN_NUMBER IN 
    (SELECT list_column.PUBLIC_NUMBER 
     FROM list_column 
     JOIN list_myprojects ON list_column.PROJECT_ID = list_myprojects.PUBLIC_NUMBER 
     WHERE list_myprojects.USER_ID = 'R8D0F1Z6h6W5K9g8n4U5' ) 
AND list_myprojects.USER_ID = 'R8D0F1Z6h6W5K9g8n4U5'

As you can see in the following image the results are duplicated and wrong because the same TASK_NUMBER belongs to two PROJECT_NUMBER different.

I hope you have explained me well

mysql

asked by gmarsi 01.12.2017 в 11:27

source

1 answer

How can I get the duration of a video using mediainfo.dll? VB.NET How to install the QT Designer module in Linux (Ubuntu)

score 0 · Accepted Answer

I think your scheme is not well designed for four reasons

Tables have names that are confusing ( list_r there is no way to know which refers to user entities)

The tables seem to have a primary key INTERN_NUMBER that does not always identify the entity, but this in some cases is identified via PUBLIC_NUMBER . All entities should be identified with their INTERN_NUMBER .

Columns that relate entities have confusing names. ( list_myprojects.PUBLIC_NUMBER should be at least list_myprojects.PROJECT_NUMBER )

If a user can access a project created by another, whose columns were potentially created by others, and whose tasks some may have been created by others, you do not have relational integrity through the USER_ID and that field should be only referential.

I imagine that you inherited this scheme as it comes and there is nothing to do, but with these conditions it is difficult to understand what the query does. That said, it seems to me that you raise it wrong by doing JOIN between list_myprojects and list_task at the beginning, considering that you want to list the access to other entities and not the authorship of the entity (which seems to be the meaning of list_task.USER_ID ).

I would pose the query as:

Tráeme todos los elementos de 'list_myprojects' del usuario 'R8D0F1Z6h6W5K9g8n4U5' 
  Y de esta tabla sus proyectos relacionados
    Y de éstos sus columnas relacionadas
      Y de éstas sus tareas relacionadas

This would be

SELECT t.PUBLIC_NUMBER AS TASK_NUMBER, 
       t.NAME AS TASK_NAME, 
       p.NAME AS PROJECT_NAME, 
       p.PUBLIC_NUMBER AS PROJECT_NUMBER
FROM list_myprojects mp
JOIN list_project p ON p.PUBLIC_NUMBER = mp.PUBLIC_NUMBER
LEFT JOIN list_column c ON c.PROJECT_ID = p.PUBLIC_NUMBER 
LEFT JOIN list_task t ON t.COLUMN_NUMBER = c.PUBLIC_NUMBER       
WHERE mp.USER_ID = 'R8D0F1Z6h6W5K9g8n4U5'

And this will only work if list_project.PUBLIC_NUMBER , list_column.PUBLIC_NUMBER and list_task.PUBLIC_NUMBER are unique keys.

In practice this should also include foreign keys

list_column.PROJECT_ID referencing list_project.PUBLIC_NUMBER
list_task.COLUMN_NUMBER referencing list_column.PUBLIC_NUMBER
list_myprojects.PUBLIC_NUMBER referencing list_project.PUBLIC_NUMBER
list_myprojects.USER_ID referencing list_r.INTERN_NUMBER