Select child records from mysql tables
I got the following example of a choice which got tier 2 records, the problems got deeper, can anyone help?
SELECT
id_mobile AS ID_PROJETO,
UM.qtd_UC,
AM.qtd_AMBIENTE
FROM projetos_mobile AS PM
LEFT JOIN (
SELECT
COUNT(id) AS qtd_UC,
projeto,
data_hora_importacao,
id_uc_mobile
FROM ucs_mobile
WHERE data_hora_importacao = '2015-05-15 17:21:02'
GROUP BY projeto) AS UM
ON PM.id_mobile = UM.projeto
LEFT JOIN (
SELECT
COUNT(id_uc_mobile) AS qtd_AMBIENTE,
id_uc_mobile
FROM ucs_mobile
LEFT JOIN (
SELECT
uc
FROM ambientes_mobile AS s
WHERE data_hora_importacao = '2015-05-15 17:21:02') AS G
ON G.uc = ucs_mobile.id_uc_mobile
WHERE data_hora_importacao = '2015-05-15 17:21:02') AS AM
ON UM.id_uc_mobile = AM.id_uc_mobile
WHERE PM.data_hora_importacao = '2015-05-15 17:21:02'
http://sqlfiddle.com/#!9/2eecf
here is a sqlfiddle in case anyone wants to try a solution. I have a specific hierarchy: projeto> uc> ambiente> secao> medicoes
ucs_mobile.projeto refers to projetos_mobile.id_mobile
ambientes_mobile.uc refers to ucs_mobile.id_uc_mobile
secoes_iluminacao_mobile.ambiente refers to ambientes_mobile.id_ambiente_mobile
I need to count each child for a parent that I am passing, I will have 5 functions that will return the counter of each child for a given parent, for example projeto parent which should have count (ucs), count (ambientes), count (secoes ), count (medicoes)
So, I hope you guys can help me. The database is ugly ugly but this is what I got. Appreciate any help.
source to share
When you have really big requests like this, it can often be helpful to break them down individually, starting from scratch and patch them together.
I started by simply counting each ucs_mobile row for each projetos_mobile value. You can do this by joining the two tables on the corresponding row and using COUNT(DISTINCT um.id)
to get the number of rows. There are other ways to do this, but this particular method will scale better for the rest of your request:
SELECT pm.id, COALESCE(COUNT(DISTINCT um.id), 0) AS qty_uc
FROM projetos_mobile pm
LEFT JOIN ucs_mobile um ON um.data_hora_importacao = '2015-05-15 17:21:02' AND um.projeto = pm.id_mobile
GROUP BY pm.id;
The COALESCE function will be used to fill 0 samples. Until you forget to use the keyword DISTINCT
and group correctly id
, you can simply add child rows like this:
SELECT
pm.id,
COALESCE(COUNT(DISTINCT um.id), 0) AS qty_uc,
COALESCE(COUNT(DISTINCT am.id), 0) AS qty_am,
COALESCE(COUNT(DISTINCT sim.id), 0) AS qty_sim
FROM projetos_mobile pm
LEFT JOIN ucs_mobile um ON um.data_hora_importacao = '2015-05-15 17:21:02' AND um.projeto = pm.id_mobile
LEFT JOIN ambientes_mobile am ON am.data_hora_importacao = um.data_hora_importacao AND am.uc = um.id_uc_mobile
LEFT JOIN secoes_iluminacao_mobile sim ON sim.data_hora_importacao = am.data_hora_importacao AND sim.ambiente = am.id_ambiente_mobile
GROUP BY pm.id;
Here is a SQL Fiddle example . NOTE I have slightly modified your example data to make sure my query works as expected.
Also, side of the note. I noticed that you continued to use the same date in your suggestions WHERE
, so I just joined each table on the date, and also made sure that in my very first join I was looking for the specified date, which will be queued to be pushed to other tables.
source to share