many-to-many SQL 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

SQL Server 2000 Forums

SQL Server Development (2000)

many-to-many SQL Query

Author

Topic

animations
Starting Member

5 Posts

Posted - 2006-01-25 : 13:13:28

I have created a many-to-many database...


Categories    Company       Join_Cat
---------     ---------     --------
Cat_ID*       Comp_ID*      Join_ID*
Cat_Name      Comp_Name     Cat_ID
                            Comp_ID

The Join_Cat table joins the Company to different various Categories.

What I am trying to do is return all categories that a company IS NOT listed in ...
(or, filter out the categories that they are already in from categories)

a: I can return all Categories company 1 are in, using the code below...


SELECT * FROM Categories
WHERE Cat_ID IN
(SELECT DISTINCT Join_Cat.Cat_ID FROM Join_Cat WHERE Join_Cat.Comp_ID = 1)

But, when I try to reverse the results to not equal to (<> 1) It return all Categories. I'd thought this would returnk all categories that company 1 are not in)

I have tried joins and Subqueries, but to no avail.

Any help would be appreciated.

SERVER MODEL:
PHP and MySQL v5

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-25 : 13:37:10

Use a cross-join to create a list off all category/company combinations as a subquery:

select	CatCompList.Cat_ID,
	CatCompList.Comp_ID
from	--CatCompList
	(select	Categories.Cat_ID,
		Company.Comp_ID
	from	Categories,
		Company) CatcompList
	left outer join Join_Cat
		on CatCompList.Cat_ID = Join_Cat.Cat_ID
		and CatCompList.Comp_ID = Join_Cat.Comp_ID
where	Join_Cat.Comp_ID is null

animations
Starting Member

5 Posts

Posted - 2006-01-25 : 14:23:22

Thanks for the feedback. However, I still have problems with the SQL. To help me get this working, could you please explain what is happening in this query.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-25 : 16:02:13

Subquery creates as dataset called CatCompList, using a cross-join to return all possible combinations of Category and Company.

CatCompList is then left-joined to Join_cat, which holds all existing combinations of Category and Company.

The WHERE clause filters out only thos CatCompList combinations with no matching record in Join_cat.

animations
Starting Member

5 Posts

Posted - 2006-01-25 : 18:03:31

Thanks for your help - it's much appreciated . . . .

I have found that the QUERY below works . . . . .

SELECT *
FROM Categories
WHERE Cat_ID NOT IN (SELECT DISTINCT Join_Cat.Cat_ID
FROM Join_Cat WHERE Join_Cat.Comp_ID = 1)

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-25 : 23:41:26

That works well for a single company. My method produces a list of all companies with missing categories. So, depends upon your requirements. Good luck.

animations
Starting Member

5 Posts

Posted - 2006-01-26 : 09:17:56

How would I convert the subquery to a JOIN for compatability with MySQL v4.0.x?

SELECT *
FROM Categories
WHERE Cat_ID NOT IN (SELECT DISTINCT Join_Cat.Cat_ID
FROM Join_Cat WHERE Join_Cat.Comp_ID = 1)

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-26 : 10:38:41

quote:
SELECT Distinct
Categories.*
FROM Categories
LEFT OUTER JOIN Join_Cat
on Categories.Cat_ID = Join_Cat.Cat_ID
and Join_Cat.Comp_ID = 1
WHERE Join_Cat.Cat_ID is null

The IN() and NOT IN() functions are generally discouraged for this sort of thing, as they are considered less efficient than the LEFT JOIN method. There are those who will argue both sides, however.

animations
Starting Member

5 Posts

Posted - 2006-01-26 : 11:02:08

quote:

SELECT Distinct
Categories.*
FROM Categories
LEFT OUTER JOIN Join_Cat
on Categories.Cat_ID = Join_Cat.Cat_ID
and Join_Cat.Comp_ID = 1
WHERE Join_Cat.Cat_ID is null

Again, thank you. This works perfectly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources