Select with Inner Join

Asked

Viewed 244 times

0

Good afternoon,
I am making an app where I need to list all rooms of a user and the last recorded consumption of each room, only that my code search wrong, if a room has more than one consumption it duplicates the same, then I would like a help to tidy up my sql command. Follow prints of how it looks if you have more than one consumption per room:inserir a descrição da imagem aqui

The code I’m using:
SELECT co.id,co.descricao,ca.potencia_atual from comodo co INNER JOIN consumo_atual ca ON ca.Comodo_id = co.id WHERE co.Usuario_id = :id

Schematic print of the two tables:
inserir a descrição da imagem aqui

php mysql ionic3

1 answer

1


You can include the GROUP BY, grouping together the search results.

SELECT co.id,co.descricao,ca.potencia_atual from comodo co 
INNER JOIN consumo_atual ca ON ca.Comodo_id = co.id 
WHERE co.Usuario_id = :id
GROUP BY co.id,co.descricao,ca.potencia_atual

  • Unfortunately did not, what changed was that now the duplicated rooms were grouped, I would like to appear only the rooms that the user has more the last consumption recorded in that room.

  • I didn’t understand what you wouldn’t like to see in the search result, but what is showing up. Are there rooms that the user doesn’t have? Or isn’t the value of consumption appearing? Or you would like a separate output with the rooms the user has and another with consumption?

  • if the user has more than one consumption registered in the same room in the comic, in the home list will list the comodo more than once, one for each consumption recorded in the comic, so for example, if the room has 2 consumptions it will appear twice in the home, and i would like q to appear only once listing only the last comodo record.

  • I think I got it. You need to first check what is the last record value of each room in the current consume_table and then do the Inner Join. See if this solution works: SELECT co.id,co.descricao,ca.potencia_atual from comodo co 
INNER JOIN
(select comodo_id, potencia_atual, MAX(data_criacao) as maxdata from consumo_atual
gruop by comodo_id, potencia_atual) ca
ON ca.Comodo_id = co.id
WHERE co.Usuario_id = :id

  • unfortunately it did not work, I will search a little in the documentation

Browser other questions tagged php mysql ionic3

You are not signed in. Login or sign up in order to post.