Joining and excluding results?

[imstillatwork]

Not sure how to word this, but I'll try to exlpain it as well as I can.

First lets start with the tables.
Three of them. two objects and a link/join table to join the two objects together (two objects being pages and items)

Pages table:
pageID, page details..

Items table:
ItemID, item details..

pageItems table:
PageID,ItemID

So it's a standard many relation.

Given a PageID I need all of the items that do NOT hava relation to that pageID. initially easy enough but if an item is related to more then one page - it still shows up in my results and I don't want that item at all.


So If I am working with PageID = 1
and there are the following rows in the pageItem table
pageID | ItemID
1 1
1 2
1 3
2 1
2 2

I want, given pageID 1, only to return itemID 3.







Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[imstillatwork]

I think I got it using

SELECT [fields]
FROM items AS i
INNER JOIN pageItems AS pi ON pi.itemID = i.itemID
GROUP BY pi.itemID
HAVING pi.pageid != 1

Seems to do it.



Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[imstillatwork]

maybe that only appeared to work...
Back to the original post then...


Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[imstillatwork]

SELECT [fields]
FROM items AS i
INNER JOIN pageItems AS pi ON pi.itemID = i.itemID
GROUP BY pi.itemID
HAVING pi.pageid
NOT IN(SELECT itemID FROM pageItems WHERE pageID = 1)

This works I THINK.... So far the results are good.

Is there a better way? Am I going about this wrong?

Thank!

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[elsenorjose]

The problem is that 'all of the items that do NOT have a relation to that pageID' doesn't make a lot of sense in your example. What makes the 1:3 relation valid but not the other ones?

Think of it this way and it might help frame the problem better: What DOES define a relation. If the definition changes from ID to ID then it's going to be very difficult to implement this in SQL. However, if there is a logical relation, then it's merely a matter of excluding any values that do not meet that definition.