1. ## 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?

2. Code:
```SELECT vid FROM vehicle_tire_fitment
UNION
SELECT vid FROM vehicle_wheel_fitment```

3. 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. 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. 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,

6. 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?

7. Luis, you should have said you were using mysql (and FYI there's a separate forum for it)

8. 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.

#### Posting Permissions

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