Tricky query? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Tricky query?

Author

Topic

jonnyc
Starting Member

10 Posts

Posted - 2009-02-20 : 07:30:49

I am struggling to find a way to create the results I want. I wonder if any expect out there would be able to help.

I have three tables, let's simplfy things and call them product, category and xref.
So,
product table contains information about products and has two fields: product_id, product_description
category table has two fields: category_id and category_name. It links products to categories, a product usually exists in more than one category so there are multiple entries with the same product_id and different category_id
xref table has two fields: category_id and product_id and is a
one-to-one match of category_id to category_name

The query is created through a search. So, product category names are supplied in the search and the aim is to retrieve the product information for all products but only if they exist in ALL of the specified categories.

Seems tricky to me. Can anyone help?

sridhar.dbe
Starting Member

34 Posts

Posted - 2009-02-20 : 07:53:12

is this what you want

select p.productid,p.productname from product p
inner join xref x on x.productid=p.productid
inner join category c on c.category_id =x.category_id
where c.category_name='xxxxx'
and p.product_name='xxxx'

isk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 09:19:40

[code]
SELECT p.product_id,p.product_description
FROM product p
INNER JOIN xref x
ON x.product_id=p.product_id
CROSS JOIN (SELECT COUNT(DISTINCT categoryid) AS CatCount FROM category) c
GROUP BY p.product_id,p.product_description,c.CatCount
HAVING COUNT(DISTINCT x.category_id) =c.CatCount
[/code]

jonnyc
Starting Member

10 Posts

Posted - 2009-02-21 : 07:12:40

select p.productid,p.productname from product p
inner join xref x on x.productid=p.productid
inner join category c on c.category_id =x.category_id
where c.category_name='xxxxx'

Many thanks, this is very tidy and looks promising but what about the case where more than one category_name is supplied? In this case I want all products selected only if they exist in all category names.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-21 : 08:36:20

Did you look at visakh's solution?

jonnyc
Starting Member

10 Posts

Posted - 2009-02-21 : 09:16:56

I tried it as it is and it returns an empty set. There is no mention of category names in his query so I don't know how this would select based on category names supplied...unless I am missing something.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-21 : 10:47:49

Maybe:

SELECT p.product_id,p.product_description
FROM product p
INNER JOIN xref x
ON x.product_id=p.product_id
INNER JOIN Category c
ON c.categoryid = x.categoryid
Group by p.product_id,p.product_description
HAVING COUNT(DISTINCT x.category_id) = COUNT(distinct c.categoryname)

jonnyc
Starting Member

10 Posts

Posted - 2009-02-21 : 11:00:55

Not quite, maybe the explanation of the requirements wasn't clear enough.

With the original table and field description...
For example, I have ten different categories and there are many products each of which can be in more than one category. If I specify three category names I want to retrieve only the products that exist in all three of these categories.

jonnyc
Starting Member

10 Posts

Posted - 2009-02-21 : 11:53:21

This may simplify things. I can get a list of products and category names in which they belong:

SELECT x.product_id, c.category_name
FROM xref x
INNER JOIN category c ON c.category_id = x.category_id
WHERE c.category_name = 'xxxx'
OR c.category_name = 'yyyy'

which brings back all products that exist in category xxxx or category yyyy

So, if this brings back the table:
product_id category_name
23 xxxx
24 xxxx
25 xxxx
23 yyyy
26 yyyy
27 yyyy
only product that exists in BOTH categories is 23...this is the only one I want back. So, how do I pull the product_id from this if the product_id exists for both of these category names?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-21 : 12:14:21

I think this will do it:

Select Product_id 
from 
(SELECT x.product_id, c.category_name
FROM xref x
INNER JOIN category c ON c.category_id = x.category_id
WHERE c.category_name in (@category_name,...))Z
Group by Product_id
Having Count(distinct category_name)=(Select Count(distinct category_name) from Category)

jonnyc
Starting Member

10 Posts

Posted - 2009-02-21 : 13:50:51

Are the brackets right in this, there seems to be one missing?

jonnyc
Starting Member

10 Posts

Posted - 2009-02-21 : 14:51:21

Let me take this one stage at a time. Looking at the internal bits:

SELECT x.product_id, c.category_name
FROM xref x
INNER JOIN category c ON c.category_id = x.category_id
WHERE c.category_name in ("xxxx","yyyy","zzzz")
group by product_id having count(distinct category_name)=3

This brings back the required infomation for this part of the query, ie. the product_id that exists in all categories: xxxx, yyyy and zzzz. I put in the '3' on the end as it wasn't working like this:

SELECT x.product_id, c.category_name
FROM xref x
INNER JOIN category c ON c.category_id = x.category_id
WHERE c.category_name in ("xxxx","yyyy","zzzz")
group by product_id having count(distinct category_name)=(select count(distinct category_name))

So, the problem in this part appears to be the (select count(distinct category_name) not bringing back the right number. Does category_name need a prefix?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-21 : 14:58:46

Did you run the query I have given? why are you changing?

jonnyc
Starting Member

10 Posts

Posted - 2009-02-21 : 15:18:38

Because, in mysql, I ran it, no records returned and no error:

select product_id from
(SELECT x.product_id, c.category_name
FROM xref x
INNER JOIN category c ON c.category_id = x.category_id
WHERE c.category_name in ('xxxx','yyyy'))Z
group by product_id
having count(distinct category_name)=(select count(distinct category_name) from category)

I did this in phpmyadmin. Oddly, it didn't even tell me there were 0 records returned.

jonnyc
Starting Member

10 Posts

Posted - 2009-02-21 : 15:22:37

...but if I change it, replacing the last part:

(select count(distinct category_name) from jos_vm_category)

with 2 (because I am specifying two categories)

...then it works!

So, I cannot understand it. I am using mysql 5.0

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-21 : 15:22:57

This forum is for SQL Server.You have to post in MYSQL forum like dbforums.com.I have no idea how you code in MySQL.

jonnyc
Starting Member

10 Posts

Posted - 2009-02-21 : 16:02:05

OK, thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources