SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Miami, Florida - Caracas, Venezuela
    Posts
    379
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multitable Left Join

    Lets see how this should be done...

    If I have five tables:

    vehicle
    vid

    vehicle_tire_fitment
    vid
    tid

    tires
    tid

    vehicle_wheel_fitment
    vid
    wid

    wheel
    wid

    And I want to show all the vehicles that have AT LEAST one wheel OR tire. How do I join the tables? I know how to join two tables but here I got lost.

    I want to select all vid that appear at least once in either vehicle_tire_fitment.vid OR vehicle_wheel_fitment.vid.

    Does anyone know?
    Luis

  2. #2
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT vid FROM vehicle_tire_fitment
    UNION
    SELECT vid FROM vehicle_wheel_fitment

  3. #3
    SitePoint Addict -TheDarkEye-'s Avatar
    Join Date
    Mar 2001
    Location
    canada
    Posts
    286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT
      v.vid
    FROM
      vehicle v,
      vehicle_tire_fitment vtf,
      vehicle_wheel_fitment vwf
    WHERE
      v.vid = vtf.vid
    OR
      v.vid = vwf.vid
    i think this will work...

  4. #4
    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)
    sorry darkeye, yes it will work, but it's a poor solution

    when you have three tables in the join, start with a three-way cross-product, every row of A combined with every row of B and every row of the AxB combination with every row of C

    included in that grand cross-product will be rows of A which have a vid value that does not match the value of B or C, or just one of them, or even both -- but the majority of rows in the cross-product will be rows where the A and B and C values of the vid columns do not match

    out of all those rows, the ones your query wants to keep are the ones where A.vid equals either B.vid or C.vid

    this is going to give you certain A.vid values over, and over, and over...

    so at the very least you need DISTINCT, and if you had included that, then your query would have come up with the right answer, but much less efficiently than redemption's

    cleverly hidden in redemption's solution is removal of duplicates too, in the fact that it uses UNION instead of UNION ALL

    however, there's an order of magnitude fewer dupes to remove

    note also how redemption did not need to access the A table at all, since all that was required was the vid numbers

    there is actually an order of magnitude fewer rows being joined, too

    i hope this was helpful, i do not usually tear apart solutions like this, and i certainly meant no offence

    rudy

  5. #5
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Miami, Florida - Caracas, Venezuela
    Posts
    379
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks everyone.

    I'm sorry to say I'm using MySQL 3.23.36.

    I can't use UNION.

    We will upgrade soon but for now I need to make it work some other way.

    Thank you,
    Luis

  6. #6
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Miami, Florida - Caracas, Venezuela
    Posts
    379
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now r973,

    I get it when you sugest doing (AxB)xC but how do I retain and access the results of AxB so I can run the query that gives me (AxB)xC?
    Is that possible?
    Luis

  7. #7
    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)
    Luis, you should have said you were using mysql (and FYI there's a separate forum for it)

    use -TheDarkEye-'s query, add DISTINCT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict -TheDarkEye-'s Avatar
    Join Date
    Mar 2001
    Location
    canada
    Posts
    286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    sorry darkeye, yes it will work, but it's a poor solution

    when you have three tables in the join, start with a three-way cross-product, every row of A combined with every row of B and every row of the AxB combination with every row of C

    included in that grand cross-product will be rows of A which have a vid value that does not match the value of B or C, or just one of them, or even both -- but the majority of rows in the cross-product will be rows where the A and B and C values of the vid columns do not match

    out of all those rows, the ones your query wants to keep are the ones where A.vid equals either B.vid or C.vid

    this is going to give you certain A.vid values over, and over, and over...

    so at the very least you need DISTINCT, and if you had included that, then your query would have come up with the right answer, but much less efficiently than redemption's

    cleverly hidden in redemption's solution is removal of duplicates too, in the fact that it uses UNION instead of UNION ALL

    however, there's an order of magnitude fewer dupes to remove

    note also how redemption did not need to access the A table at all, since all that was required was the vid numbers

    there is actually an order of magnitude fewer rows being joined, too

    i hope this was helpful, i do not usually tear apart solutions like this, and i certainly meant no offence

    rudy

    damn! thats like the hundredth time i forgot the distinct! i need to staple your post to my forhead. you know, its funny cause every time i do that i can just tell something isnt right...

    no offense taken, unless you are suggesting that i didnt know redemption's query would work (it sounded like you where). i just wanted to post the table join solution cause thats what lgomez asked for.


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
  •