• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

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;
0
hdcowboyaz
Asked:
hdcowboyaz
  • 3
1 Solution
 
hdcowboyazAuthor Commented:
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';
0
 
hdcowboyazAuthor Commented:
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';
0
 
hdcowboyazAuthor Commented:
I figured it out
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now