Problem with queries in related databases

Question

Problem with queries in related databases

Navigation

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

0

Good morning

I am trying to make a query in which one 3 tables and I want to be able to list the tasks assigned to a user with their corresponding Tag, but I duplicate the tasks that contain more than one Tag .

The code is as follows:

* task_asignation
id taskId asignedBy asignedTo state priority
1    1       6          6       1        1
2    2       6          6       1        1
3    3       1          2       1        1
4    4       2          3       1        1
5    5       6          1       1        1

* task
taskId title  createdBy editedBy
1     prueba1    6         6 
2     prueba2    6         6 
3     Prueba3    1         1
4     Prueba4    2         2 
5     Prueba5    6         6 

* task_tag
id taskId      name      createdBy
1    1       pruebas        6
2    2         test         6
3    3     pruebas/test     1
5    5      Software        6
6    5      hardware        6

Model:

public function getAsignedTasks($employeeId){
    $query = $this->db->select('*')->from('task_asignation')->join('task', 'taskId', 'INNER JOIN')->join('task_tag', 'taskId', 'LEFT JOIN')->where('asignedBy', $employeeId)->get();


    if ($query->num_rows() > 0) {
        return $query;
    }else{
        return array();
    }   
}

Controller:

$tasksAsignedData   = $this->Mtasks->getAsignedTasks($data['employeeId']);
$data['tasksAsigned'] = $tasksAsignedData->result();

View:

 foreach ($tasksAsigned as $key => $value) {
              echo $value->title;
              echo $value->name;
}

As a result I get:

Tarea de prueba 1
pruebas

Tarea de prueba 2
test

Prueba 5
Software

Prueba 5
hardware

codeigniter mysqli

asked by Korzan 16.03.2017 в 13:39

source

2 answers

0

Good to avoid redundancy that is what I understand you, you must use GROUP BY at the end, you must define a parameter that you do not want to repeat and group them for that, in your case the task id, so these records would not be repeated ; another option is to play with LEFT JOIN and RIGHT JOIN to join the tables since depending on which one you use the will not bring the repeated data from the table on the left or right; and another option is to use DISTINCT so that it does not bring you the repeated ones.

with DISTINCT:

Select DISTINCT (idtarea), other fields ..... from table1 join table2 join table3 on table.id = table2.id ...... etc.

with GROUPBY:

Select idtarea, ..... from table1 join table2 join table3 on table.id = table2.id ...... etc. group by idtarea.

answered by 16.03.2017 в 15:09

Back to erase subviews of a TableView after a reload? Collapse with Bootstrap 3 and jQuery does not work

score 2 · Accepted Answer

To be able to obtain the Hardware and Software records in a single line, you would have to use or SUBQUERY or group them and use GROUP_CONCAT .

Here are some guides:

SELECT t.title, GROUP_CONCAT(tt.name SEPARATOR ',') FROM task_asignation ta 
   INNER JOIN task t ON t.taskId = ta.taskId
    LEFT JOIN task_tag tt ON tt.taskId = ta.taskId
     GROUP BY t.title

or

SELECT ta.*,
     (SELECT GROUP_CONCAT(tt.name SEPARATOR ',') FROM task_tag tt 
      WHERE tt.taskId = ta.taskId) tag_name
  FROM task_asignation ta 
 INNER JOIN task t ON t.taskId = ta.taskId

NOTE : These queries do not test them, but it's an idea of how you should arm them to get what you need.

I'm sending you the function reference URL GROUP_CONCAT