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.
[FONT=verdana]You know, you’ve almost answered your own question:
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
Translate that to SQL, and you get something like this:
SELECT prodId, prodTitle FROM tblproducts
WHERE prodId <> X AND prodID NOT IN (SELECT relprodId2 FROM tblprodrelated)
I know that’s not exactly what you asked for. That’s mainly because I can’t quite figure out the last bit of your request (it seems redundant to me). You might use parantheses to make it clearer.
However, this above should start you off the right direction.
Thanks for your help - I’m not too hot on nesting SELECT statements within SELECT statements which is why I got stuck, but using your suggestion worked. Here’s my final MySQL query:
SELECT prodId, prodTitle FROM tblproducts WHERE prodId <> X AND prodID NOT IN (SELECT relprodId2 FROM tblprodrelated WHERE relprodId = X) AND prodID NOT IN (SELECT relprodId FROM tblprodrelated WHERE relprodId2 = X) ORDER BY prodTitle ASC
[FONT=verdana]Glad you got it working, Scim. I was in a bit of a hurry when I posted my suggestion, and didn’t take the time to do the whole thing. But you’ve clearly got the hang of it.
The official term for this type of construct is a sub-select. The rule is that it must always return exactly one column. I find it helps to think of that column as a single-column table.