asked on

Query Syntax

For the query below pc.pcac needs to bring in the query... (SELECT pcac FROM pcac pc JOIN (SELECT MAX(pcac_id) AS pcac_id FROM pcac WHERE deleted = 'N' GROUP BY estdetail_id) AS pc2 ON pc2.pcac_id = pc.pcac_id;)

SELECT est.est_id 'EST ID', est.etype 'TYPE', (d.I_AMT - d.G_AMT) 'TAX & SHIP', pc.pcac FROM estdetail d
LEFT JOIN pcac pc ON (pc.estdetail_id = d.estdetail_id)
LEFT JOIN (
emp e
JOIN est ON (e.emp_id = est.emp_id_created)
) ON (est.est_id = d.est_id)
LEFT JOIN (
funvert f
LEFT JOIN task t ON (t.funvert_id = f.funvert_id)
LEFT JOIN releases r ON (r.releases_id = t.releases_id)
LEFT JOIN project pj ON (pj.project_id = r.project_id)
LEFT JOIN stdtask s ON (s.stdtask_id = t.stdtask_id)
) ON (t.task_id = d.task_id)
LEFT JOIN (
icat ic
LEFT JOIN item i ON (ic.icat_id = i.icat_id)
LEFT JOIN itype it ON (it.itype_id = i.itype_id)
LEFT JOIN mc ON (mc.mc_id = i.mc_id)
) ON (i.item_id = d.item_id)
WHERE (d.deleted != 'Y' OR d.deleted IS NULL)
AND est.estatus != 'Denied'
AND est.estatus != 'Cancelled'
AND pj.project_id = 2624;

hdcowboyaz

ASKER

This report is off the estdetail table and pulls in a field from the pcac table. Its suppose to pull in the pc.pcac field grouped by pc.estdetail_id where the pc.pcac_id is MAX.

The query below doesn't error out but pulls in two values instead of one.

SELECT est.est_id 'EST ID', d.estdetail_id, pc.pcac,
@PCAC := (SELECT pcac FROM pcac pc WHERE deleted = 'N' AND pc.estdetail_id = d.estdetail_id ORDER BY pc.pcac_id DESC LIMIT 1) PCAC
FROM estdetail d
LEFT JOIN pcac pc ON (pc.estdetail_id = d.estdetail_id)
LEFT JOIN (
emp e
JOIN est ON (e.emp_id = est.emp_id_created)
) ON (est.est_id = d.est_id)
LEFT JOIN (
funvert f
LEFT JOIN task t ON (t.funvert_id = f.funvert_id)
LEFT JOIN releases r ON (r.releases_id = t.releases_id)
LEFT JOIN project pj ON (pj.project_id = r.project_id)
LEFT JOIN stdtask s ON (s.stdtask_id = t.stdtask_id)
) ON (t.task_id = d.task_id)
LEFT JOIN (
icat ic
LEFT JOIN item i ON (ic.icat_id = i.icat_id)
LEFT JOIN itype it ON (it.itype_id = i.itype_id)
LEFT JOIN mc ON (mc.mc_id = i.mc_id)
) ON (i.item_id = d.item_id)
WHERE (d.deleted != 'Y' OR d.deleted IS NULL)
AND est.estatus != 'Denied'
AND est.estatus != 'Cancelled';

hdcowboyaz

ASKER

It's the LEFT JOIN pcac pc ON (pc.estdetail_id = d.estdetail_id) line that's making it double up. I think that can be safely removed.

The @PCAC := (SELECT ...) part, that part ought to always save the very latest PCAC value for the given estdetail_id, which we're then using in the arithmetic in the other columns.

Try this:

SELECT est.est_id 'EST ID', d.estdetail_id, @PCAC := (SELECT pcac FROM pcac pc WHERE deleted = 'N' AND pc.estdetail_id = d.estdetail_id ORDER BY pcac_id DESC LIMIT 1) PCAC
FROM estdetail d
LEFT JOIN (
emp e
JOIN est ON (e.emp_id = est.emp_id_created)
) ON (est.est_id = d.est_id)
LEFT JOIN (
funvert f
LEFT JOIN task t ON (t.funvert_id = f.funvert_id)
LEFT JOIN releases r ON (r.releases_id = t.releases_id)
LEFT JOIN project pj ON (pj.project_id = r.project_id)
LEFT JOIN stdtask s ON (s.stdtask_id = t.stdtask_id)
) ON (t.task_id = d.task_id)
LEFT JOIN (
icat ic
LEFT JOIN item i ON (ic.icat_id = i.icat_id)
LEFT JOIN itype it ON (it.itype_id = i.itype_id)
LEFT JOIN mc ON (mc.mc_id = i.mc_id)
) ON (i.item_id = d.item_id)
WHERE (d.deleted != 'Y' OR d.deleted IS NULL)
AND est.estatus != 'Denied'
AND est.estatus != 'Cancelled';

ASKER CERTIFIED SOLUTION

hdcowboyaz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial