From: | Sim Zacks <sim(at)compulab(dot)co(dot)il> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | union query returning duplicates |
Date: | 2004-10-20 11:22:22 |
Message-ID: | 465014500.20041020132222@compulab.co.il |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I am using 8.0 beta 1 on an RH 8 Linux server.
I have a union query that I am converting from access (where it
worked) and it is returning duplicates. The only difference between
the two rows is the Row field, which is returned automatically.
and an example of a row that it has returned duplicate. I have
verified that the row only shows up 1 time in each select statement
when run individually.
Here is a sample of the duplicates it returned (one was row 2 and the
other row 3, but that didn't seem to come with a copy and paste from
pgadmin):
2;"486CORE-D16-F4-C66-N0-R3-S-E";6.6;5.274;97;3;6.6
2;"486CORE-D16-F4-C66-N0-R3-S-E";6.6;5.274;97;3;6.6
Below is the query:
SELECT a.assemblyid, a.assemblyname, b.fixedprice,
CASE
WHEN sum(packagecount) <> totalcount::numeric THEN NULL::double precision
ELSE sum(calculatedprice)
END AS calcprice, b.supplierid, a.productid, COALESCE(b.fixedprice,
CASE
WHEN sum(packagecount) <> totalcount::numeric THEN NULL::double precision
ELSE sum(calculatedprice)
END) AS activeprice
FROM assemblies a
JOIN qry_assemblyfixedprices b ON a.assemblyid = b.assemblyid
LEFT JOIN qry_assemblycalcprices c ON c.supplierid = b.supplierid AND b.assemblyid = c.assemblyid
WHERE b.supplierid =97
GROUP BY a.assemblyid, a.assemblyname, b.fixedprice, b.supplierid, totalcount, a.productid
order by assemblyid
UNION
SELECT a.assemblyid, a.assemblyname, c.fixedprice,
CASE
WHEN sum(packagecount) <> totalcount::numeric THEN NULL::double precision
ELSE sum(calculatedprice)
END AS calcprice, b.supplierid, a.productid, COALESCE(c.fixedprice,
CASE
WHEN sum(packagecount) <> totalcount::numeric THEN NULL::double precision
ELSE sum(calculatedprice)
END) AS activeprice
FROM assemblies a
JOIN qry_assemblycalcprices b ON a.assemblyid = b.assemblyid
LEFT JOIN qry_assemblyfixedprices c ON c.supplierid = b.supplierid AND c.assemblyid = b.assemblyid
WHERE b.supplierid =97
GROUP BY a.assemblyid, a.assemblyname, c.fixedprice, b.supplierid, totalcount, a.productid
order by assemblyid
Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax
From | Date | Subject | |
---|---|---|---|
Next Message | Sim Zacks | 2004-10-20 11:54:04 | Re: union query returning duplicates |
Previous Message | Magnus Hagander | 2004-10-20 08:51:47 | Re: beta3 winxp initdb problems |