asked on

Need to return a count of Vendors by SKU as a field in a result list the skus of a single vendor.

Table contains multiple Vendors with multplie SKUs. I currently have a query that pulls on a single vendor listing only those skus which are also listed by other vendors (See attached sql statement.) Now in addition to what gets returned I want to add a field to the result set that returns a COUNT of how many Vendors list that same SKU. Once that gets accomplished I want to pass a parameter that will query on that count as well. For vendor "A" list all skus that are listed by 3 or more vendors. Right now I am getting a list of all skus that are listed by other vendors but I need a count by SKU of those vendors and later the ability to query on the count.

Thanks in advance

select *
from merchant_to_product
where fk_merchant_id = 373
AND price between 0 AND 300
AND fk_subcategory_id = 89
AND (merchant_to_product.sku IN
                          (SELECT     sku
                            FROM      merchant_to_product,merchant
                            WHERE     fk_merchant_id = merchant_id
			 AND merchant.is_active = 1					 AND fk_merchant_id <> 373))

Open in new window

Guy Hengel [angelIII / a3]

I would do like this:

select mp.*
, (select count(*) from merchant_to_product,merchant
                            WHERE     fk_merchant_id = merchant_id
                   AND merchant.is_active = 1                               AND fk_merchant_id <> 373)
  other_merchant_count
from merchant_to_product p
where fk_merchant_id = 373
AND price between 0 AND 300
AND fk_subcategory_id = 89
AND (merchant_to_product.sku IN
                          (SELECT     sku
                            FROM      merchant_to_product,merchant
                            WHERE     fk_merchant_id = merchant_id
                   AND merchant.is_active = 1                               AND fk_merchant_id <> 373))

Open in new window

BlueKnight66

ASKER

Thanks for your replay but the query reurns an error.
I had to change the "m" in the from line to "mp" to match the "mp.*" in the select line which killed a lot of the error but still getting the one below.
"The column prefix 'merchant_to_product' does not match with a table name or alias name used in the query."

Any help will be greatly appreciated.

Thanks

Guy Hengel [angelIII / a3]

sorry for that... got distracted :(

select mp.*
, ( SSELECT COUNT(*)
                 FROM merchant im
                 JOIN merchant_to_product imp
                   ON imp.fk_merchant_id = im.merchant_id
                  AND imp.fk_merchant_id <> 373
                WHERE im.merchant.is_active = 1
                  AND imp.sku = mp.sku
              ) other_merchant_count
from merchant_to_product mp
where mp.fk_merchant_id = 373
AND mp.price between 0 AND 300
AND mp.fk_subcategory_id = 89
AND mp.sku IN (SELECT imp.sku
                 FROM merchant im
                 JOIN merchant_to_product imp
                   ON imp.fk_merchant_id = im.merchant_id
                  AND imp.fk_merchant_id <> 373
                WHERE im.merchant.is_active = 1
              )

Open in new window

BlueKnight66

ASKER

Great!!!! I had to make a minor adjustment in the where clause (" im.merchant.is_active" to "im.is_active") and then it returned what I wanted.

NOW, the next step, how would I query that based on the count? Return only those with 5 or more in the count field.

Hey thanks so much. Question why the aliases for the tables? I apprecaite the statement but want to know why it works too.

select mp.*
, ( SSELECT COUNT(*)
                 FROM merchant im
                 JOIN merchant_to_product imp
                   ON imp.fk_merchant_id = im.merchant_id
                  AND imp.fk_merchant_id <> 373
                WHERE im.is_active = 1
                  AND imp.sku = mp.sku
              ) other_merchant_count
from merchant_to_product mp
where mp.fk_merchant_id = 373
AND mp.price between 0 AND 300
AND mp.fk_subcategory_id = 89
AND mp.sku IN (SELECT imp.sku
                 FROM merchant im
                 JOIN merchant_to_product imp
                   ON imp.fk_merchant_id = im.merchant_id
                  AND imp.fk_merchant_id <> 373
                WHERE im.is_active = 1
              ) 
Open in New Window

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

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

Start Free Trial

Guy Hengel [angelIII / a3]

> Question why the aliases for the tables?

to make it clear which column comes from which table/alias (especially for any reader later)
in case of (correlate) subqueries, so make sure which value is taken.
for correlated subqueries, to ensure you link to the correct table/alias

>I appreciate the statement but want to know why it works too.

check out this part of the query:

select mp.*
, ( SELECT COUNT(*)
                 FROM merchant im
                  <...>
                 WHERE ... 
                   AND imp.sku = mp.sku   <<< this line correlates the subquery, and does the count only for the "mp.sku" for the current row of the outer query
  )

Open in new window

BlueKnight66

ASKER

That did the trick!

Thanks a lot. I was trying variations of what you gave over the last few hours but all without aliases hence my cry for help. Thanks so much!

I have another question along the same lines as this query, would you want me to ask it here or post a new one.

using the same table, return a column with a ranking based on price. Basically sku, number of vendors(which you already accomplished), but now ranking. Then of course passing a parameter quering rank. Give me all skus with more than 4 vendors where I rank 2 or better.

Thanks again!!!!!!

Guy Hengel [angelIII / a3]

with sql 2005+, ranking is "easy" with the row_number() , or rank() functions.
with sql 2000, this will be tricky

BlueKnight66

ASKER

Thanks for all your help. I'm sure I will have more questions has I dive deeper into this project. I will post my follow up question about ranking in SQL 2000. Thanks again and have a good night.