SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    list from one table where ID is not in another table

    Hi,

    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.

  2. #2
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,541
    Mentioned
    63 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    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.

    Mike






  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Mike,

    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

    Thanks again for your help.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by scim1971 View Post
    relId - INT AutoIncrement - Primary Key
    relprodId - INT (the ID of a product)
    relprodId2 - INT (the ID of the product it is related to)
    remove the unnecessary auto_increment id, and make the remaining two columns a composite primary key

    then also add an index on the two columns in the reverse order
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,541
    Mentioned
    63 Post(s)
    Tagged
    0 Thread(s)
    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.

    Mike



  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    actually, subselects like that can also have multiple columns, provided that the database engine supports "row constructors"

    for instance...
    Code:
    WHERE ( column1,column2 ) IN ( SELECT foo, bar FROM sometable )
    haven't tested this in mysql, but mysql does support row constructors in the INSERT statement...
    Code:
    INSERT INTO mytable ( col1,col2 ) VALUES ( 'humpty','dumpty' ) , ( 'frick','frack' ) ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,541
    Mentioned
    63 Post(s)
    Tagged
    0 Thread(s)
    subselects like that can also have multiple columns, provided that the database engine supports "row constructors"
    Good point. I've only ever used row constuctors in an INSERT (in SQL Server 2008, not in MySQL). I'd forgotten you could to it with a sub-select.

    Mike


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •