Query Help

SQL DB Query Help
I have several suppliers of products.
When a customer does a Search
I want to return only the Lowest cost items by Part Number.
The query could return many different Part Numbers.
I also need to return Description, Part Number, Qty on Hand, Supplier etc.
These are all in the table.
Note that Description, Qty and Supplier are usually different.
Example items in DB
Part Desc Cost Qty Supplier
123 Widget 1.00 10 1
123 A Widget 2.00 5 2
123 Widget A 3.00 20 3
567 B Widget 9.00 3 1
567 Widget B 8.00 17 2
567 Widget 12.00 8 3

I would like to return
Part Desc Cost Qty Supplier
123 Widget 1.00 10 1
567 Widget B 8.00 17 2

Thanks in advance

Perhaps something like this:

SELECT
P.* FROM Parts P
INNER JOIN
(
SELECT P3.part as PartNum, MIN(P3.Cost) as MinCost
FROM Parts P3
GROUP BY P3.Part) AS P2
ON P.Part = P2.partnum
AND P.cost = P2.mincost

Query Help

Hi,
I have two tables
Products
--
ProductId
ProductTitle
Bids
--
BidId
ProductId
BidAmount
BidderName
I want to grab all the products with the highest bidAmount and the BidderNam
e.
There could be multiple bidders for each product.Try:
select
p.ProductID
, p.ProductTitle
, b.BidID
, b.BidAmount
, b.BidderName
from
Products p
join
Bidders b on b b.ProductID = p.ProductID
where
b.BidAmount =
(
select
max (b2.BidAmount)
from
Bidders b2
where
b2.ProductID = p.ProductID
)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"dotnettester" <dotnettester@.discussions.microsoft.com> wrote in message
news:D60ADC91-6F30-49EE-A50C-63E3CAF9649A@.microsoft.com...
> Hi,
> I have two tables
> Products
> --
> ProductId
> ProductTitle
> Bids
> --
> BidId
> ProductId
> BidAmount
> BidderName
> I want to grab all the products with the highest bidAmount and the
> BidderName.
> There could be multiple bidders for each product.
>sql

query help

I have Two tables. Products and ProductInfo.
All the Products(productId) have entries in ProductInfo (FK productId)
I want to find out products whose entries do not exist in ProductInfo
Pls hlpSELECT ProductID --, ...
FROM Products p
LEFT OUTER JOIN ProductInfo pi
ON p.ProductID = pi.ProductID
WHERE pi.ProductID IS NULL
-- or
SELECT ProductID --, ...
FROM Products
WHERE ProductID NOT IN
(
SELECT ProductID
FROM ProductInfo
)
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
news:4939AE3D-7B71-418F-A6E6-8AD4DBD56A8F@.microsoft.com...
>I have Two tables. Products and ProductInfo.
> All the Products(productId) have entries in ProductInfo (FK productId)
> I want to find out products whose entries do not exist in ProductInfo
> Pls hlp|||Hi
SELECT <column lists> FROM Products WHERE NOT EXISTS
(SELECT * FROM ProductInfo WHERE Products.ProductId=ProductInfo.Productid )
"mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
news:4939AE3D-7B71-418F-A6E6-8AD4DBD56A8F@.microsoft.com...
> I have Two tables. Products and ProductInfo.
> All the Products(productId) have entries in ProductInfo (FK productId)
> I want to find out products whose entries do not exist in ProductInfo
> Pls hlp|||SELECT ...
FROM Product AS p
WHERE NOT EXISTS(SELECT * FROM ProductInfo AS pi WHERE pi.productID = p.Prod
uctId)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
news:4939AE3D-7B71-418F-A6E6-8AD4DBD56A8F@.microsoft.com...
>I have Two tables. Products and ProductInfo.
> All the Products(productId) have entries in ProductInfo (FK productId)
> I want to find out products whose entries do not exist in ProductInfo
> Pls hlp|||Hi , Aaron
> SELECT ProductID --, ...
> FROM Products
> WHERE ProductID NOT IN
> (
> SELECT ProductID
> FROM ProductInfo
> )
>
I have my doubt about this query because if the OP has ProductId IS NULL
we may get back a wrong output .Am I right
BTW why did you drop an acoount (ng) ? :-)
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:ux6n8fLUFHA.1796@.TK2MSFTNGP15.phx.gbl...
> SELECT ProductID --, ...
> FROM Products p
> LEFT OUTER JOIN ProductInfo pi
> ON p.ProductID = pi.ProductID
> WHERE pi.ProductID IS NULL
> -- or
> SELECT ProductID --, ...
> FROM Products
> WHERE ProductID NOT IN
> (
> SELECT ProductID
> FROM ProductInfo
> )
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
> "mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
> news:4939AE3D-7B71-418F-A6E6-8AD4DBD56A8F@.microsoft.com...
>|||> I have my doubt about this query because if the OP has ProductId IS NULL
> we may get back a wrong output .Am I right
Possibly, but it was defined as a foreign key, so I figured the chance of it
being NULL was pretty small.

> BTW why did you drop an acoount (ng) ? :-)
Because there are a couple of dimwits in here who don't seem to know when to
shut up. Not you, of course.
A|||Yep, I sow a few posts. Sometimes it is really sorry that this NG is
unmoderated , should be a moderator here.
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:egwAZpLUFHA.2892@.TK2MSFTNGP14.phx.gbl...
NULL
> Possibly, but it was defined as a foreign key, so I figured the chance of
it
> being NULL was pretty small.
>
> Because there are a couple of dimwits in here who don't seem to know when
to
> shut up. Not you, of course.
> A
>|||I agree with AB. Some people in the newsgroup dont know how to respect the
SQL MVPs. They contradict them, argue with them for no apparent reason and
talk to them in a demeaning way. I am not a MVP but I personally think
everyone should respect all the SQL MVPs because of their tremendous
knowledge and experience which they share to perform better in our jobs.
They give great design solutions, great answers and then actually point the
flaws when we are doing something wrong.
I read all of MVP posts and I always follow and implement their suggestions.
I am hoping others would learn to respect them.
Thanks
M
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEwG7gLUFHA.3840@.tk2msftngp13.phx.gbl...
> SELECT ...
> FROM Product AS p
> WHERE NOT EXISTS(SELECT * FROM ProductInfo AS pi WHERE pi.productID =
p.ProductId)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
> news:4939AE3D-7B71-418F-A6E6-8AD4DBD56A8F@.microsoft.com...
>

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 builder: how to do a LIKE parameter

hi,

i can do

"select * from products where name = @.name" kind of statements in query builder

but

"select * from products where name LIKE @.name" dosen't work!"

any ideas? i'm using sql server express.

thanks

That statement should look like this:

"select *from productswhere nameLIKE'%' + @.name +'%'"

|||

thanks!! it worked.

just wondering, if i have been using query builder and sql statements to construct tableadapters, would the application be vulnerable to sql injection attacks like classic asp?

|||Yes. Refer toHow To: Protect From SQL Injection in ASP.NET for details.