Three joins with three tables

[caherner]

The following code gets me 90% of the results I require. Hopefully this is simple for someone, but I cannot figure out how to make a third JOIN, if that is the way to go. Currently I have a JOIN between products and rentals, and a JOIN between rentals and rentalsets, but I also need a JOIN between rentalsets and products...something like LEFT JOIN rentalsets ON rentalsets.productID = products.productID. But I do not know where to place some code to join rentalsets.productID to products.productID in the statement below.

strSQL = "SELECT rentals.line, rentals.username, rentals.dateadded, rentals.movie, rentals.rentalout, rentals.rentalin, rentals.active, rentals.rank, products.productID, products.productName, rentalsets.productID, rentalsets.subproductID, rentalsets.description " & _
"FROM products " & _
"RIGHT JOIN (rentals LEFT JOIN rentalsets " & _
"ON rentalsets.subproductID = rentals.movie) " & _
"ON products.productID = rentals.movie " & _
"ORDER BY rentals.username, rentals.rank asc"

Thanks in advance.

 

[r937]

any time there's a mix of RIGHT and LEFT joins in the same query, i get totally confused

could you run SHOW CREATE TABLE for the three tables please?

also, are you dumping the entire database, or will you have some sort of WHERE condition for this query?

r937.com | rudy.ca

 

[caherner]

Here are my three tables with the columns in question and sample data:

Products

ProductID ProductName
00001234 Test1
00001235 Test2
00001236 Test3

Rentals

MovieNo User MovieID
1 test@test.com 00001235
2 test@test.com 00001235-2
3 test@test.com 00001234

RentalSets

ProductID SubProductID Description
00001235 00001235-1 Disc 1
00001235 00001235-2 Disc 2
00001235 00001235-3 Disc 3


Here's what I need the query's end result to look like:

User ProductName MovieID Description
test@test.com Test2 00001235
test@test.com Test2 00001235-2 Disc 2
test@test.com Test1 00001234

Everything from Rentals shows in the query, but only the corresponding data from Products and RentalSets appears.

Any help appreciated. Thanks.

 

[r937]

select rentals.line
     , rentals.username
     , rentals.dateadded
     , rentals.movie
     , rentals.rentalout
     , rentals.rentalin
     , rentals.active
     , rentals.rank
     , products.productID
     , products.productName
     , rentalsets.productID
     , rentalsets.subproductID
     , rentalsets.description  
  from rentals
inner
  join rentalsets
    on rentalsets.subproductID = rentals.movie
inner
  join products    
    on products.productID = rentalsets.ProductID  
order 
    by rentals.username
     , rentals.rank

r937.com | rudy.ca

 

[caherner]

The end result is it only shows all fields in the table RentalSets and not the table Rentals. I need it to show all fields in Rentals, instead of RentalSets.

Thanks for that attempt. Any other suggestions?

 

[r937]

did you mean to say all rows?

you said all fields

because there are columns from all three tables in the result

r937.com | rudy.ca

 

[caherner]

I apologize for my terminology, I did mean all rows.

 

[r937]

okay, change INNER to LEFT OUTER in both joins



r937.com | rudy.ca

 

[caherner]

Ok, it's almost there but the ProductName does not show for anything in the rentals table since there is no join between rentals.movie = products.productID. Where does the third join fit in?

 

[r937]

i can only go by what you've shown as your data

from what you've shown, i joined the tables the way they should be joined

sorry

r937.com | rudy.ca

 

[caherner]

I actually came up with a solution tonight. After everyone's help and suggestions I saw this was impossible to combine all in one query, so I had a brainwave and broke it into 2 queries. I used my original query, and then in an if statement farther down in my page, if the productName isnull then do a query between the products and rentalsets tables getting the productName this way. All in all it works exactly how I need it too.

I would like to thank you for your hard work and attempts to help me.