Query Help

Hi there,
I need some help with a query that is giving duplicate results. The
query looks like this:
SELECT products.idProduct, products.sku, products.description,
products.price, products.highPrice, products.listhidden,
products.listprice, products.serviceSpec, products.bToBPrice,
products.smallImageUrl, products.noprices, products.sDesc,
products.stock, products.noStock, products.pcprod_HideBTOPrice,
products.FormQuantity, products.pcProd_BackOrder, products.IDBrand,
Brands.BrandName, products.showInHome FROM products JOIN
categories_products ON
products.idProduct=categories_products.idProduct LEFT OUTER JOIN
Brands ON products.IDBrand=Brands.IDBrand WHERE active=-1 AND
configOnly=0 and removed=0 AND (products.idProduct = 6936 OR
products.idProduct = 6800 OR products.idProduct = 6935 OR
products.idProduct = 8531 OR products.idProduct = 8778 OR
products.idProduct = 16881 OR products.idProduct = 16057 OR
products.idProduct = 16587 OR products.idProduct = 16880 OR
products.idProduct = 18160) ORDER BY products.showInHome ASC,
products.visits DESC, products.sales DESC
The problem is that a product can appear in more than 1 category so
the same idProduct may appear more than once in the
categories_products table. I need to use that join in the statement,
however, as sometimes I add this to the query to further filter
products:
"AND categories_products.idCategory = 160"
I've tried to do a GROUP BY but with no success, what I really want to
do is make this query only pull distinct idProduct but obviously I
cannot just add "SELECT DISTINCT" when there is more than 1 field in
the query. Any ideas?
-DrewIf I understand what you want, you could just add a DISTINCT to the select.
However, the way I would do it is to remove the join on categories_products
and rewrite the query as (note that I also changed that long list of OR's to
an IN). Using the list of OR's will work, but the IN is simpler and easier
to understand (at least to me).
SELECT products.idProduct,
products.sku,
products.description,
products.price,
products.highPrice,
products.listhidden,
products.listprice,
products.serviceSpec,
products.bToBPrice,
products.smallImageUrl,
products.noprices,
products.sDesc,
products.stock,
products.noStock,
products.pcprod_HideBTOPrice,
products.FormQuantity,
products.pcProd_BackOrder,
products.IDBrand,
Brands.BrandName,
products.showInHome
FROM products
LEFT OUTER JOIN Brands ON products.IDBrand=Brands.IDBrand
WHERE active=-1
AND configOnly=0
and removed=0
AND products.idProduct IN (6936, 6800, 6935, 8531, 8778, 16881, 16057,
16587, 16880, 18160)
ORDER BY products.showInHome ASC, products.visits DESC, products.sales DESC
Then if you need to add the condition that the idProduct is found in the
categories_products table with a categories_products.idCategory = 160, just
add the following to the WHERE clause:
AND EXISTS (SELECT * FROM categories_products
WHERE products.idProduct=categories_products.idProduct
AND categories_products.idCategory = 160)
Tom
"Drubage" <drew@.tribalectic.com> wrote in message
news:fa067b56-5b9d-400d-b111-5c10057cb2b6@.i29g2000prf.googlegroups.com...
> Hi there,
> I need some help with a query that is giving duplicate results. The
> query looks like this:
> SELECT products.idProduct, products.sku, products.description,
> products.price, products.highPrice, products.listhidden,
> products.listprice, products.serviceSpec, products.bToBPrice,
> products.smallImageUrl, products.noprices, products.sDesc,
> products.stock, products.noStock, products.pcprod_HideBTOPrice,
> products.FormQuantity, products.pcProd_BackOrder, products.IDBrand,
> Brands.BrandName, products.showInHome FROM products JOIN
> categories_products ON
> products.idProduct=categories_products.idProduct LEFT OUTER JOIN
> Brands ON products.IDBrand=Brands.IDBrand WHERE active=-1 AND
> configOnly=0 and removed=0 AND (products.idProduct = 6936 OR
> products.idProduct = 6800 OR products.idProduct = 6935 OR
> products.idProduct = 8531 OR products.idProduct = 8778 OR
> products.idProduct = 16881 OR products.idProduct = 16057 OR
> products.idProduct = 16587 OR products.idProduct = 16880 OR
> products.idProduct = 18160) ORDER BY products.showInHome ASC,
> products.visits DESC, products.sales DESC
> The problem is that a product can appear in more than 1 category so
> the same idProduct may appear more than once in the
> categories_products table. I need to use that join in the statement,
> however, as sometimes I add this to the query to further filter
> products:
> "AND categories_products.idCategory = 160"
> I've tried to do a GROUP BY but with no success, what I really want to
> do is make this query only pull distinct idProduct but obviously I
> cannot just add "SELECT DISTINCT" when there is more than 1 field in
> the query. Any ideas?
> -Drew|||On Mar 31, 5:59=A0pm, "Tom Cooper"
<tomcoo...@.comcast.no.spam.please.net> wrote:
> If I understand what you want, you could just add a DISTINCT to the select=.
> However, the way I would do it is to remove the join on categories_product=s
> and rewrite the query as (note that I also changed that long list of OR's =to
> an IN). =A0Using the list of OR's will work, but the IN is simpler and eas=ier
> to understand (at least to me).
> SELECT products.idProduct,
> =A0products.sku,
> =A0products.description,
> =A0products.price,
> =A0products.highPrice,
> =A0products.listhidden,
> =A0products.listprice,
> =A0products.serviceSpec,
> =A0products.bToBPrice,
> =A0products.smallImageUrl,
> =A0products.noprices,
> =A0products.sDesc,
> =A0products.stock,
> =A0products.noStock,
> =A0products.pcprod_HideBTOPrice,
> =A0products.FormQuantity,
> =A0products.pcProd_BackOrder,
> =A0products.IDBrand,
> =A0Brands.BrandName,
> =A0products.showInHome
> FROM products
> LEFT OUTER JOIN Brands ON products.IDBrand=3DBrands.IDBrand
> WHERE active=3D-1
> =A0 AND configOnly=3D0
> =A0 and removed=3D0
> =A0 AND products.idProduct IN (6936, 6800, 6935, 8531, 8778, 16881, 16057,=
> =A0 =A0 16587, 16880, 18160)
> ORDER BY products.showInHome ASC, products.visits DESC, products.sales DES=C
> Then if you need to add the condition that the idProduct is found in the
> categories_products table with a categories_products.idCategory =3D 160, j=ust
> add the following to the WHERE clause:
> AND EXISTS (SELECT * FROM =A0categories_products
> =A0 =A0 =A0WHERE products.idProduct=3Dcategories_products.idProduct
> =A0 =A0 =A0 =A0 =A0AND categories_products.idCategory =3D 160)
> Tom
> "Drubage" <d...@.tribalectic.com> wrote in message
> news:fa067b56-5b9d-400d-b111-5c10057cb2b6@.i29g2000prf.googlegroups.com...
>
> > Hi there,
> > I need some help with a query that is giving duplicate results. The
> > query looks like this:
> > SELECT products.idProduct, products.sku, products.description,
> > products.price, products.highPrice, products.listhidden,
> > products.listprice, products.serviceSpec, products.bToBPrice,
> > products.smallImageUrl, products.noprices, products.sDesc,
> > products.stock, products.noStock, products.pcprod_HideBTOPrice,
> > products.FormQuantity, products.pcProd_BackOrder, products.IDBrand,
> > Brands.BrandName, products.showInHome FROM products JOIN
> > categories_products ON
> > products.idProduct=3Dcategories_products.idProduct LEFT OUTER JOIN
> > Brands ON products.IDBrand=3DBrands.IDBrand WHERE active=3D-1 AND
> > configOnly=3D0 and removed=3D0 AND (products.idProduct =3D 6936 OR
> > products.idProduct =3D 6800 OR products.idProduct =3D 6935 OR
> > products.idProduct =3D 8531 OR products.idProduct =3D 8778 OR
> > products.idProduct =3D 16881 OR products.idProduct =3D 16057 OR
> > products.idProduct =3D 16587 OR products.idProduct =3D 16880 OR
> > products.idProduct =3D 18160) ORDER BY products.showInHome ASC,
> > products.visits DESC, products.sales DESC
> > The problem is that a product can appear in more than 1 category so
> > the same idProduct may appear more than once in the
> > categories_products table. I need to use that join in the statement,
> > however, as sometimes I add this to the query to further filter
> > products:
> > "AND categories_products.idCategory =3D 160"
> > I've tried to do a GROUP BY but with no success, what I really want to
> > do is make this query only pull distinct idProduct but obviously I
> > cannot just add "SELECT DISTINCT" when there is more than 1 field in
> > the query. Any ideas?
> > =A0 -Drew- Hide quoted text -
> - Show quoted text -
Worked like a charm, thank you so much!
-Drew

Query for most recent of duplicate records

I need some ideas on this query.
I have a table with entries similar to the following with columns name,
id, and timestamp.
kmyoung 345 2005-08-22 07:29:00.000
kmyoung 345 2005-08-29 07:29:15.000
mphillips 360 2005-08-27 14:48:18.000
rbeheler 360 2005-08-22 09:29:11.000
rbeheler 360 2005-08-24 09:28:19.000
rbeheler 360 2005-08-29 09:27:54.000
I need a resultant set that gives me the records with the most recent
timestamp for each ID as listed below.
kmyoung 345 2005-08-29 07:29:15.000
rbeheler 360 2005-08-29 09:27:54.000
Thanks for the help.Try,
select
*
from
t1 as a
where
c3 = (select max(b.c3) from t1 as b where b.[id] = a.[id])
go
AMB
"Jeff" wrote:

> I need some ideas on this query.
> I have a table with entries similar to the following with columns name,
> id, and timestamp.
> kmyoung 345 2005-08-22 07:29:00.000
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-22 09:29:11.000
> rbeheler 360 2005-08-24 09:28:19.000
> rbeheler 360 2005-08-29 09:27:54.000
> I need a resultant set that gives me the records with the most recent
> timestamp for each ID as listed below.
> kmyoung 345 2005-08-29 07:29:15.000
> rbeheler 360 2005-08-29 09:27:54.000
> Thanks for the help.
>|||select name, id, max(timestamp) as timestamp
from thetable
group by name, id
having count(*)>1 -- if you need just the ones that have dupes, add this
line
Jeff wrote:
> I need some ideas on this query.
> I have a table with entries similar to the following with columns name,
> id, and timestamp.
> kmyoung 345 2005-08-22 07:29:00.000
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-22 09:29:11.000
> rbeheler 360 2005-08-24 09:28:19.000
> rbeheler 360 2005-08-29 09:27:54.000
> I need a resultant set that gives me the records with the most recent
> timestamp for each ID as listed below.
> kmyoung 345 2005-08-29 07:29:15.000
> rbeheler 360 2005-08-29 09:27:54.000
> Thanks for the help.
>|||It worked great as long as t1 was an actual table. But in actuality t1 is a
union of two tables. When I substitute (select * from t1 union select * fro
m
t2) as t1, it no longer works. Would it be possible to rewrite this with a
subquery instead of t1?
"Alejandro Mesa" wrote:
> Try,
> select
> *
> from
> t1 as a
> where
> c3 = (select max(b.c3) from t1 as b where b.[id] = a.[id])
> go
>
> AMB
> "Jeff" wrote:
>|||Very close, but I ended up with this resultant set instead.
kmyoung 345 2005-08-29 07:29:15.000
mphillips 360 2005-08-27 14:48:18.000
rbeheler 360 2005-08-29 09:27:54.000
I ended up with two entries for id 360.
"Trey Walpole" wrote:

> select name, id, max(timestamp) as timestamp
> from thetable
> group by name, id
> having count(*)>1 -- if you need just the ones that have dupes, add this
> line
>
> Jeff wrote:
>|||Nevermind. It worked fine by simply substituting the subquery in place of
t1. Works exactly as I need it to .
Thanks!
"Jeff" wrote:
> It worked great as long as t1 was an actual table. But in actuality t1 is
a
> union of two tables. When I substitute (select * from t1 union select * f
rom
> t2) as t1, it no longer works. Would it be possible to rewrite this with
a
> subquery instead of t1?
> "Alejandro Mesa" wrote:
>|||oops - seeing a little cross-eyed today...
Jeff wrote:
> Very close, but I ended up with this resultant set instead.
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-29 09:27:54.000
> I ended up with two entries for id 360.
> "Trey Walpole" wrote:
>

query assistance -return most recent date

I have a table that has two fields, pkg_num, which is a number, and
del_date_time, which is a date-time. The table can contain duplicate pkg_num
values, as long as the del_date_time values are different for any given
number. I need a query that will return the most recent del_date_time for
each pkg_num. Any ideas?
On Thu, 10 Feb 2005 09:17:01 -0800, Rich_A2B wrote:

>I have a table that has two fields, pkg_num, which is a number, and
>del_date_time, which is a date-time. The table can contain duplicate pkg_num
>values, as long as the del_date_time values are different for any given
>number. I need a query that will return the most recent del_date_time for
>each pkg_num. Any ideas?
Hi Rich_A2B,
Probably
SELECT pkg_num, MAX(del_date_time)
FROM MyTable
GROUP BY pkg_num
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||That works, thanks! Now to complicate things, I have a third field,
DEL_RECIP_NAME. There can exist records where PKG_NUM is the same, but both
DEL_DATE_TIME and DEL_RECIP_NAME are different. How do I show all three
fields in the query result, but only show records with the most recent
DEL_DATE_TIME?
"Hugo Kornelis" wrote:

> On Thu, 10 Feb 2005 09:17:01 -0800, Rich_A2B wrote:
>
> Hi Rich_A2B,
> Probably
> SELECT pkg_num, MAX(del_date_time)
> FROM MyTable
> GROUP BY pkg_num
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Fri, 11 Feb 2005 08:35:07 -0800, Rich_A2B wrote:

>That works, thanks! Now to complicate things, I have a third field,
>DEL_RECIP_NAME. There can exist records where PKG_NUM is the same, but both
>DEL_DATE_TIME and DEL_RECIP_NAME are different. How do I show all three
>fields in the query result, but only show records with the most recent
>DEL_DATE_TIME?
Hi Rich_A2B,
I guess I should have seen that one coming :-)
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
WHERE NOT EXISTS (SELECT *
FROM MyTable AS b
WHERE b.pkg_num = a.pkg_num
AND b.del_date_time > a.del_date_tim)
or
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
INNER JOIN (SELECT pkg_num, MAX(del_date_time) AS max_del_date_time
FROM MyTable
GROUP BY pkg_num) AS b
ON a.pkg_num = b.pkg_num
AND a.del_date_time = b.max_del_date_time
or
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
WHERE a.del_date_time = (SELECT MAX(del_date_time)
FROM MyTable AS b
WHERE b.pkg_num = a.pkg_num)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||

Quote:

Originally posted by Hugo Kornelis
On Fri, 11 Feb 2005 08:35:07 -0800, Rich_A2B wrote:

>That works, thanks! Now to complicate things, I have a third field,
>DEL_RECIP_NAME. There can exist records where PKG_NUM is the same, but both
>DEL_DATE_TIME and DEL_RECIP_NAME are different. How do I show all three
>fields in the query result, but only show records with the most recent
>DEL_DATE_TIME?
Hi Rich_A2B,
I guess I should have seen that one coming :-)
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
WHERE NOT EXISTS (SELECT *
FROM MyTable AS b
WHERE b.pkg_num = a.pkg_num
AND b.del_date_time > a.del_date_tim)
or
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
INNER JOIN (SELECT pkg_num, MAX(del_date_time) AS max_del_date_time
FROM MyTable
GROUP BY pkg_num) AS b
ON a.pkg_num = b.pkg_num
AND a.del_date_time = b.max_del_date_time
or
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
WHERE a.del_date_time = (SELECT MAX(del_date_time)
FROM MyTable AS b
WHERE b.pkg_num = a.pkg_num)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)