How to view inverted rows from many-to-many table

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

How to view inverted rows from many-to-many table

Author

Topic

kamau
Starting Member

2 Posts

Posted - 2008-03-11 : 10:53:12

Hi, my name's John and this is my first post here at SQL team. I've learnt a lot from the forums here, never needed to post though. I'm pretty good at sql, but not that good and I think for the first time in couple of years I now have a query that I'm completely unable to create.
Let me explain the setup.
I've got three tables e.g.

Products (productid, Name)
100 Computer
200 Printer
300 PDA

ProductActions (ID, Name)
6 changed casing
8 Changed motherboard
7 replaced cd-drive

ProductPerformedActions (productid, actionid)
100,7
100,9
200,8

This basically shows a simple setup showing that product (computer) has been done the action "repaired" and product (printer) has been done the action "changed motherboard". The query i'd like to have is where I can select all products that have not been performed on cerrtain actions. e.g. a list of products that have not been performed on all actions, such as

UnPerformedActionsList(product,actionid)
PDA,6
PDA,7
PDA,8
Printer,7
Printer,6
Computer,8

I know its foundation is something like this:


select productid from products where productid not in 
	(select productid from productperformedactions....

which elimiantes products that have been performed an action on, but it also removes it for ALL actions,
which i'm trying to avoid. All i want is kind of a cross join, listing all actions, then inverting it, to show
all products that have NOT had it performed on them.
I hope this makes sense.

Thanks
John

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 10:57:42

[code]SELECT p.ProductID,
pa.ID
FROM Products AS p
CROSS JOIN ProductActions AS pa[/code]will give you all combinations of Products and ProductActions.[code]SELECT p.ProductID,
pa.ID
FROM Products AS p
CROSS JOIN ProductActions AS pa
LEFT JOIN ProductPerformedActions AS ppa ON ppa.ProductID = p.ProductID
AND ppa.ActionID = pa.ID
WHERE ppa.ProductID IS NULL[/code]will give you all combinations "minus" those already stored.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-11 : 11:00:04

SELECT p.productid,pa.actionid
FROM Products p
CROSS JOIN ProductActions pa
LEFT OUTER JOIN ProductPerformedActions ppa
ON ppa.productid=p.productid
AND ppa.actionid=pa.ID
WHERE ppa.productid IS NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 11:04:31

I think there is an internet echo somewhere...

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-11 : 11:05:13

quote:
Originally posted by Peso

I think there is an internet echo somewhere...

E 12°55'05.25"
N 56°04'39.16"

I have felt it myself a lot of times before

kamau
Starting Member

2 Posts

Posted - 2008-03-11 : 11:08:36

WOW! That has got to be the fastest set jof replies I've ever seen in my life.
I just tested it out and it works perfect. Thank you Peso and Visakh16, your help is
much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources