SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist captainccs's Avatar
    Join Date
    Mar 2004
    Location
    Caracas, Venezuela
    Posts
    516
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Using field data in an ON clause

    MySQL: Using field data in an ON clause

    I'm trying to do a variation on Bill Karwin's "ClosureTable" using a self-join instead of an extra table. For this I'm putting the ids I want to find in an extra field in the main table:

    Code:
    CREATE TABLE IF NOT EXISTS `test` (
      `id` smallint(6) NOT NULL auto_increment,
      `name` varchar(64) NOT NULL default '',
      `father_id` smallint(6) NOT NULL default '0',
      `mother_id` smallint(6) NOT NULL default '0',
      `father_ids` text NOT NULL,
      `mother_ids` text NOT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
    The query is as follows:

    Code SQL:
    SELECT p.id, p.name, f.father_ids, f.mother_ids
    FROM test AS f
    CROSS JOIN test AS p ON p.id IN (f.mother_ids)
    WHERE f.id = '$id'

    If the father_ids or mother_ids field has more than one value it only matches the first one. How do I convert the field value into a proper operand for the IN clause?
    Denny Schlesinger
    web services

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    698
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Make a normalized data model instead. The first rule of normalization is to never store multiple values in a column.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    CROSS JOIN with an ON condition like yours just doesn't make sense to me (even though mysql allows it), i think it should be INNER JOIN instead

    you could use the FIND_IN_SET function for your unnormalized data, but be aware the performance sucks and the query won't scale up
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist captainccs's Avatar
    Join Date
    Mar 2004
    Location
    Caracas, Venezuela
    Posts
    516
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Normalize, yes. Thank you. But at this point I'm exploring the various ways to traverse a family tree and if "breaking the rules" improve the code, I don't mind doing it. The area where I'm having issues is in displaying all ancestors and all descendants. These are essentially linked lists which one normally handles with loops or recursive functions which I'm seeking to avoid.
    Denny Schlesinger
    web services

  5. #5
    SitePoint Evangelist captainccs's Avatar
    Join Date
    Mar 2004
    Location
    Caracas, Venezuela
    Posts
    516
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    CROSS JOIN with an ON condition like yours just doesn't make sense to me (even though mysql allows it), i think it should be INNER JOIN instead
    Yes! I pasted my experimental test code as is (sorry).

    You could use the FIND_IN_SET function for your unnormalized data, but be aware the performance sucks and the query won't scale up
    Thanks, I'll give it a try.
    Denny Schlesinger
    web services

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by captainccs View Post
    These are essentially linked lists which one normally handles with loops or recursive functions which I'm seeking to avoid.
    in that case, look up celko's nested set model

    look ma, no loops or recursion

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist captainccs's Avatar
    Join Date
    Mar 2004
    Location
    Caracas, Venezuela
    Posts
    516
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you could use the FIND_IN_SET function for your unnormalized data, but be aware the performance sucks and the query won't scale up
    It works like a charm!

    Since FIND_IN_SET() returns the position of the found element, as long as my data is in the right order it even provides the proper sort order.

    Even if "performance sucks," won't getting rid of loops and recursion make up for it?

    BTW, what do you mean "won't scale up?" Are you referring to the 64 element limit is sets? I'm using a text field for my "set"!

    in that case, look up celko's nested set model
    That's one avenue I have not yet explored. While researching this project one poster said it was awfully complicated which turned me off. But I should take a look at it even if I now have what looks like a working solution. I still have to check how difficult is to build and maintain my "sets."
    Denny Schlesinger
    web services

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    my remark about performance and not scaling applies to the unnormalized data with FIND_IN_SET

    the nested set model is ridiculously fast for selects

    the poster who said it was complicated was likely me (if your search was here on sitepoint)

    i personally don't like the backwards handsprings you need to do with the nested set model for inserts and deletes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist captainccs's Avatar
    Join Date
    Mar 2004
    Location
    Caracas, Venezuela
    Posts
    516
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the poster who said it was complicated was likely me (if your search was here on sitepoint)

    i personally don't like the backwards handsprings you need to do with the nested set model for inserts and deletes
    It was on stackOveflow: http://stackoverflow.com/a/5215988/1116878

    Fairly simple single call solution that uses an adjacency list implementation with a non recursive stored procedure. Would recommend avoiding nested sets like the plague - best left in the classroom those !
    BTW, my solution seems to be full of holes
    Denny Schlesinger
    web services


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
  •