|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| ||||||||||||||||||||||||||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
|
|
#1
January 25th, 2006, 02:38 PM
|
|||
|
|||
|
Many-to-many SQL Query
I have created a many-to-many database...
Code:
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 various Categories. What I am trying to do is return all Categories Names and ID that a company IS NOT listed in ... a: I can return all Categories that Company #1 are in. e.g. Code:
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' (<> 1) It return all Categories. I'd thought this would return 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
|
|
#2
January 25th, 2006, 04:52 PM
|
|||
|
|||
|
I can get it working in version 5 of MySQL, but not in version 4.0. Can anyone show me how to convert the sub-query to a JOIN?
Code:
SELECT * FROM Categories WHERE Cat_ID NOT IN (SELECT DISTINCT Join_Cat.Cat_ID FROM Join_Cat WHERE Join_Cat.Comp_ID = 1
|
|
#3
June 28th, 2006, 08:18 PM
|
|||
|
|||
|
Left Join?
I don't know if this will work in MySQL but it works in MS SQL 2000
SELECT cat.* FROM Categories cat LEFT JOIN dbo.Join_Cat jc ON cat.Cat_ID = jc.Cat_ID AND jc.Comp_ID = 1 WHERE jc.Cat_ID IS NULL Quote:
|
|
| Viewing: Dev Articles Community Forums > Databases > MySQL Development > Many-to-many SQL Query |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
