Print Joins data

Question

Print Joins data

Navigation

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

0

Perform the following sql, which has an inner and a left to look up the overhead and individual user data.

They are in different tables when doing the sql queries but I do not group the result to show it in the same td, resulting in this

try with group by but do not link both results to the same user

SELECT relacion,relacionu,descripcion ,nombre,nombred,gasto 
from gasto_g RIGHT JOIN usuarios  ON usuarios.relacionu= gasto_g.relacion   
             LEFT JOIN gastonocmun   ON usuarios.nombre= gastonocmun.nombred 
WHERE  MONTH( create_at) = '11'

User data

Expenditure data

The values are taken to table user attributes idusuarios, name, relationship with > expense ratio_g (this should be shown to all users) user table name is compared to the name of the gastonocmun table (this expense is shown in only shown to the user who has it loaded)

sql mysqli join phpmyadmin

asked by Anderson Rey 30.11.2018 в 06:49

source

2 answers

2

I think what you're looking for is the function GROUP_CONCAT ( link ) what it does is to group, in a single text field, all the results of a specific field, so you only get one tuple but with data from several records for the same field it is grouped in.

Your query would look something like this:

SELECT usuarios.nombre, GROUP_CONCAT(gasto_g.descripcion SEPARATOR ", " ) AS gastos
 FROM gasto_g 
RIGHT JOIN usuarios ON usuarios.relacionu = gasto_g.relacion   
 LEFT JOIN gastonocmun ON usuarios.nombre = gastonocmun.nombred 
WHERE MONTH( create_at) = '11' 
GROUP BY usuarios.id

and I would return something like:

nombre:"Irene" gastos:"gastos admin, aaaaa"
nombre:"Fidel" gastos:"gastos admin, aaaaa"

You will have to use as many GROUP_CONCAT as fields you want to show.

If you notice the operation is similar to other functions like SUM () or AVG (), only that results in a text string instead of the same type as the field used.

(edit)

Let's see if now:

SELECT usuarios.nombre as usuario, 
       GROUP_CONCAT(gasto_g.descripcion) AS gastos_comunes, 
       GROUP_CONCAT(gastonocmun.descripcion) AS gastos_no_comunes
  FROM usuarios
  LEFT JOIN gastos_g ON gasto_g.relacion = usuarios.relacionu
  LEFT JOIN gastonocmun ON gastonocmun.nombred =  usuarios.nombre
 GROUP BY usuarios.id

With this they should leave all usuarios have or do not have gastos_g or gastonocmun

I have not added the WHERE on purpose, because you do not indicate to which table it belongs or know the data to ensure that we will get results, so, to avoid rolling, the more clean the query is easier to analyze.

answered by 30.11.2018 в 10:12

Load folder files and write them to a file? How to refactor this IRC data analyzer?

score 0 · Accepted Answer

SELECT  idusuarios,relacion,relacionu,descripcion ,nombre,nombred,gasto , GROUP_CONCAT(gasto_g.descripcion SEPARATOR '-'  ) AS gastos

 
from gasto_g RIGHT JOIN usuarios  ON usuarios.relacionu= gasto_g.relacion   
             LEFT JOIN gastonocmun   ON usuarios.nombre= gastonocmun.nombred 
WHERE  MONTH( create_at) = '11'  
   GROUP BY idusuarios