Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Unanswered: How to join many to many tables

    I have 5 tables as seen in the pic. I am doing data migration from access to mysql and have to take these 5 tables down to 3. Basically I now have 3 tables but I am being told that they are not producing the correct results.

    The pic I have included is what the table design looked like before I moved any data. I figured I would start there as maybe I made a mistake when I joined the tables to get the data to copy over to the new tables.

    I used left outer joins to do this. This would be the DML I used to do it with. I'd like to know if this was correct. The requirement was to show all rows on both sides of the two join tables. Once all rows were present then I would begin to consolidate and move the data into the three new tables.

    Tables 4 and 5 are the join tables and will join tables 1, 2 and 3. The joins seem to produce the correct results but I'd like to be sure.

    Code:
    SELECT
      t1.field1,
      t1.field2,
      t3.field1,
      t3.field2,
      t5.field1,
      t5.field2
    FROM
      table1 AS t1
        Left Outer
          Join table4 AS t2
            ON t1.t1Pk1 = t2.t1Pk1
        Left Outer
          Join table2 AS t3
            ON t2.t2Pk1 = t3.t2Pk1
        Left Outer
          Join table5 AS t4
            ON t3.t2Pk1 = t4.t2Pk1
        Left Outer
          Join table3 AS t5
            ON t4.t3Pk1 = t5.t3Pk1
    Attached Thumbnails Attached Thumbnails export.jpg  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your joins look okay, at least in basic structure, although as soon as you started doing crap like table4 AS t2 and table2 AS t3 i quickly became disoriented and stopped reading the SQL

    how in the world are you going to collapse these into 3 tables -- it's just not possible!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Hey Rudy, thanks so much for the help. I agree with you about the DML being confusing. I'm sorry for modeling it that way but I was in a rush. I made the ERD and DML a bit different this time so it’s easier to follow.

    Just a little background on this project. This is a state project and I was forbidden to post any actual ERD or DML on a public forum so I made this mockup of what I'm dealing with. The managers want to migrate from access to mysql and have taken a fairly decent design IMHO and denormalized it for no reason at all. I am tasked with condensing tables 1 and 3 and deleting table2 all together. Then I need to use a join table to join to table5. There will only be a single one to many join at that point.

    I figured that what I would do is join up all of these tables the way they were in access and after I got the correct results I would take the columns I wanted and move them into a temp table and just start moving the data that way. I have 87 tables to do like this.

    One of the problems that I see is that the managers don't know anything about databases and are expecting a different result set than what my joins are currently producing. I matched my rows up to the access database and they are identical. Again, the problem is that they are not sure what they really want it seems and consequently are making me question or second guess myself. I figured I would run it by you or Pat and see what you though of this. ....Aside from it being a "cluster" of course.

    What I was told was that there is only only one column from table1 that is needed and that should be brought into table3. Every other column fron table 1 can be deleted. However, everything else in tables 3 4 and 5 is being used.

    Question.. Would I get the same result set if I changed the order of the tables when doing my left joins? Does the table order matter? I read in your book that a left join returns all rows from the left table but I am a bit confused how that should work when there are join tables involved or does this even matter?

    Code:
    SELECT
      t1.field1
      , t3.field1
      , t5.field1
    FROM
      table1 AS t1
        Left Outer
          Join table2 AS t2
            ON t1.t1Pk = t2.t1Pk
        Left Outer
          Join table3 AS t3
            ON t2.t3Pk = t3.t3Pk
        Left Outer
          Join table4 AS t4
            ON t3.t3Pk = t4.t3Pk
        Left Outer
          Join table5 AS t5
            ON t4.t5Pk = t5.t5Pk
    Attached Thumbnails Attached Thumbnails export1.jpg  

  4. #4
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Just a side note.. There is a proxy set up at work so I won't be able to respond to this thread until after I get home. I can't access anything "outside" of what the state says we can.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "cluster" is a pretty good way of describing the situation

    if, as you say, the managers don't know anything about databases, why are they presuming to dictate which tables and fields you should have?

    if i were you, i would walk away from this project

    again, your query looks okay, but regarding left joins, try running the query with inner joins and see what happens
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Based on the ERD you presented, you need to use all INNER JOIN operations, no LEFT JOINS at all. The reason for this is that all of the relationships are one-to-many, none are optional. There are no entities with zero cardinality according to your ERD, so you don't need any outer joins.

    For what it is worth, you'll get a simpler plan if you join them as 4 then 5, then 1, 2, and 3 in any convenient order. This could execute a smidgeon faster too, especially if you're using Inno-DB instead of My-ISAM.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    ...all of the relationships are one-to-many, none are optional.
    i'm going to go ahead and guess that the relationships actually might be optional, but this nuance was not reflected properly in the ERD

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    "cluster" is a pretty good way of describing the situation

    if, as you say, the managers don't know anything about databases, why are they presuming to dictate which tables and fields you should have?

    if i were you, i would walk away from this project

    again, your query looks okay, but regarding left joins, try running the query with inner joins and see what happens
    Yeah, every day that goes by the cluster factor seems more and more apparent to me.

    The managers know just enough about databases to make them dangerous. Unfortunately they don't even really know what result set they want. I spent more than an hour today with both managers that seem to contradict what the "right" result set is. One says it should be one way and the other says something different. Then they somehow seem to agree and I don’t follow their logic so I’m lost.

    This project is an absolute nightmare and if I had another project that would pay me I'd leave it in a heartbeat. I have no idea how I'm ever going to collapse these tables, as you already said.

    Yeah, the inner joins produce only those rows where there is /are matching record(s) on the other side of the join table. I was told though that they wanted to see all unmatched records as well which is why I used left joins.

    Let me ask this… Is it possible to take a field out of table1 and append it along with its corresponding data into table 3 and delete table 2?

    How I thought I would do this would be to left join the first 3 tables to get my results and then create a temp table to copy the results over to. Once the results are in the temp table I could then join the temp table to tables 4 and 5. Is this possible?

    Quote Originally Posted by r937
    i'm going to go ahead and guess that the relationships actually might be optional, but this nuance was not reflected properly in the ERD
    I think you're right Rudy, they are optional.

  9. #9
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by Pat Phelan
    Based on the ERD you presented, you need to use all INNER JOIN operations, no LEFT JOINS at all. The reason for this is that all of the relationships are one-to-many, none are optional. There are no entities with zero cardinality according to your ERD, so you don't need any outer joins.

    For what it is worth, you'll get a simpler plan if you join them as 4 then 5, then 1, 2, and 3 in any convenient order. This could execute a smidgeon faster too, especially if you're using Inno-DB instead of My-ISAM.

    -PatP
    Hey Pat, thanks for the input and direction. Actually, the ERD that I have in my possession from the state doesn't specify the cardinality either but because I have been working with the data, I can attest to the fact that ALL of these tables have optional cardinality. Tables 1 3 and 5 can have data in them with no data on the other side. This is what seems to be causing the whole misunderstanding between what they want to see.

    For what it's worth, the entire new system will be using MyISAM in a multi-user environment.
    Last edited by Frunkie; 06-24-09 at 03:05.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Frunkie
    Let me ask this… Is it possible to take a field out of table1 and append it along with its corresponding data into table 3 and delete table 2?
    possibly

    but that description of the objective is so vague that i cannot offer any further advice

    in general, you can not collapse a many-to-many relationship (table1 to table3 via the relationship in table2) without some sort of denomalizing compromise, e.g. a comma-delimited list of values in a single new column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Unfortunately the abstraction is killing us. We're to the point where what you're asking borders on: "What happens if I turn that thing in the car a couple of times?" Heck, you could turn left, be deafened by the radio, turn down the air conditioning, or throw it into four wheel drive.

    If the managers can't agree on what they want, then the problem lies in inadequate specfications. The architect stopped at too abstract a level, so that the developers/managers can imagine whatever they need or want. It sounds like they've ordered a "vehicle" and are waiting for you to provde them with one, without specifying what it needs to move or where it needs to work. There is considerable difference between a bicycle and an airplane even thogh the Wright brothers did both!

    Until you get a more solid specification that you can understand, you can't possibly deliver. The technical details are just details if you don't understand the problem to be solved.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Thanks for all the help guys.

    You're right Pat, I don't understand the problem to be solved. I'm working with a 19 year old kid who doesn't like to share details about how this system is supposed to work. I don't really think he fully understands either.

Posting Permissions

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