list from one table where ID is not in another table
This is a tricky one to explain but I hope someone can help.
I have a table used to display "related products" on a site. The table (tblprodrelated) has three fields:
relId - INT AutoIncrement - Primary Key
relprodId - INT (the ID of a product)
relprodId2 - INT (the ID of the product it is related to)
Using this method I can easily create two way (related) relationships between two products (i.e. "you may also be interested in...").
When it comes to adding products to the "tblprodrelated" table I use a simple MySQL query to create a list of existing products from the products table (tblproducts):
"SELECT prodId, prodTitle FROM tblproducts WHERE prodId <> X ORDER BY prodTitle ASC"
(X is the ID of the product I'm editing - so basically this creates a list of all the products in the database except the one I'm editing (as I wouldn't want it to create a relationship with itself).
The problem with my simple query is that the list of "other products" (to create a relationship with) always stays the same and may result in the same product being selected/related more than once (if the user doesn't manually check whether it's already been related).
What I'd like is for my simple query to be changed so that it says "get a list of the products from tblproducts where the prodId is not X (the master product) and where the prodId (ID) is not in relprodId2 (in tblprodrelated) when relprodId = X OR in relprodId (in tblprodrelated) when relprodId2 = X (as they can be either way round).
Can this be done?
The alternative could be to create an array of already related prodId's before this query and then tell this query to list products that are not in that array but I figured it would be tidier to have MySQL perform the whole query.
Many thanks to anyone who can help with this.