SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast Panda Coder's Avatar
    Join Date
    Nov 2005
    Location
    Tarifa, Spain
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    is this possible in single select

    Hi,

    I have a table with 2 INT cols

    jobNo | relatedNo

    it stores the id of a job file and the id of a related job file which is created from the job file.

    each entry creates 2 rows:
    job file id | related file id
    related file id | job file id

    since it is possible to create related job files from any job file, including related job files (they are all job files) you can get a string of related files eg
    jobNo | relatedNo
    1 | 5
    5 | 1
    5 | 18
    18 | 5
    5 | 27
    27 | 5
    27 | 32
    32 | 27
    18 | 55
    55 | 18

    Is it possible to select all the files that are connected in one select, as in the eg jobNo 1 has jobNo 5 related, 5 has 18 related, 5 also has 27 related and so on. Duplicates are fine as I can remove them later with php.

    Any pointers to tutorials much appreciated or clues as to how to construct the mysql select.

    Thanks

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Why do you have the double entries? 1-5 and 5-1, 5-18 and 18-5?

  3. #3
    SitePoint Enthusiast Panda Coder's Avatar
    Join Date
    Nov 2005
    Location
    Tarifa, Spain
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    the php was already set up to create these, I think it is to do with the job file pairs and how they are used in other parts of the system, I think I can probably change this and remove the second entry once I can select all linked files and read further into what the second entry is used for exactly.

    I am building an additional report, part of this is required to display a list of all related files for each job file shown in the report.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Why do you have the double entries? 1-5 and 5-1, 5-18 and 18-5?
    simple answer: for optimum query performance

    it is the difference between this --
    Code:
      FROM friend AS f1
    INNER
      JOIN friend AS f2
        ON f2.friend_id = f1.id
    and this --
    Code:
      FROM friend AS f1
    INNER
      JOIN friend AS f2
        ON f2.friend_id = f1.id
        OR f2.id = f1.friend_id
    you already know which query is going to be faster, don't you

    the second query is often done as a UNION because in a single SELECT, mysql can't optimize it at all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    simple answer: for optimum query performance
    Ok.
    But let's return to the OP's question: is it possible to get all related jobs in 1 query?

    If you know the max depth, yes. Right?
    But doesn't that query get more complicated with all those double entries?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by guido2004 View Post
    If you know the max depth, yes. Right?
    right

    but it doesn't get more complicated with the double entries, it stays simple
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast Panda Coder's Avatar
    Join Date
    Nov 2005
    Location
    Tarifa, Spain
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can you give me a pointer to the use of the max depth please.

    I am beginning to think this may need to be done in multiple queries. I was hoping to manage in one but I'm still pretty new to mysql so maybe I got it wrong and it needs to be multiple.

    Thanks for your help here, I'm trying to learn fast!

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Panda Coder View Post
    can you give me a pointer to the use of the max depth please.
    it's like a hierarchy of relatedness, or more like a network


    a file related to a second file is two levels

    that second file related to another file is three levels

    for each extension of the relatedness, you need another LEFT OUTER JOIN but you're alwys joining the table to itself

    up to about 15 self-joins is practical and efficient

    more than that, and you have issues even displaying the results, never mind how you retrieve them...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •