?
Solved

Multiple many-to-many joins

Posted on 2009-12-17
15
Medium Priority
?
269 Views
Last Modified: 2012-05-08
I need to know the correct and/or best way to handle a query with multiple many-to-many relationships involved.  This is to produce a report.

For a simplified example of the idea, imagine that I wanted a report showing my company's vehicles, with lists of each vehicle's options and each vehicle's drivers.

Vehicles table
========
ID     Make             Model        Year   Color
----------------------------------------------------
1      Ford              Explorer    1990    White
2      Mitsubishi      3000GT     1987   Yellow
3      Aston Martin  Vanquish  2008   Silver

Options Table
===========
ID      Text
----------------
1       Power Steering
2       Power Sun Roof
3       500W Premium Sound
4       GPS Navigation System
5       4-wheel-drive
6       CD Changer
7       Auto-dimming rear-view mirror

Drivers Table
=============
ID      Name
----------------------------
1       John Q Public
2       Jane Doe
3       Mickey Mouse
4       Donald Duck

Vehicles-to-options table
====================
Vehicle_ID        Option_ID
----------------------------------
1                        1
1                        2
1                        6
...

Vehicles-to-drivers table
====================
Vehicle_ID         Driver_ID
-------------------------------
1                        2
1                        3
...

I want the output to generate a report that has all the information together correctly:


Vehicle 1
==========
Make:   Ford
Model:  Explorer
Year:   1990
Color:   White

Options:  Power Steering
               Power Sunroof
               CD Changer

Drivers:  Mickey Mouse
               Jane Doe


But I run into unintentional cross-product problems, etc.   What's the best way?
0
Comment
Question by:tchris
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 26072643
try crystal reports
0
 
LVL 20

Expert Comment

by:gatorvip
ID: 26072646
Your join should be

Vehicles v
(left) join Vehicles-to-options vto on v.id=vto.Vehicle_ID
(left) join options o on vto.Option_ID=o.id
(left) join Vehicles-to-drivers vtd on v.id=vtd.Vehicle_ID
(left) join Drivers d on d.id=vtd.Driver_ID

0
 
LVL 1

Author Comment

by:tchris
ID: 26072654
Already using crystal reports, which has issues of its own. :-/

I really want to get this SQL syntax figured out better though.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 1

Author Comment

by:tchris
ID: 26072721
When I simply do all the left joins, I get duplicates in the results.

For example, if I have three options and only one driver:

Make: Ford
Model: Explorer
Year: 1990
Color: White

Options:  Power Steering
               Power Sunroof
               CD Changer

Driver:  Mickey Mouse
             Mickey Mouse
             Mickey Mouse

The two many-to-many tables multiply each other.
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26072732
Try this

select v.make,v.model,v.year,v.color, LTRIM (EXTRACT (XMLAGG (XMLELEMENT ("x", ',' || vo.text)), '/x/text()'), ',') ,
LTRIM (EXTRACT (XMLAGG (XMLELEMENT ("x", ',' || vd.name)), '/x/text()') from vehicle,options o,drivers d,Vehiclestooptions vo,Vehiclestodrivers where
v.id=vo.vehicle_id and
v.id=vd.vehicla_id and
o.id=vo.option_id and
d.id=vd.driver_id
group by v.make,v.model,v.year,v.color;
0
 
LVL 20

Expert Comment

by:gatorvip
ID: 26072761
>>When I simply do all the left joins, I get duplicates in the results.

I put the "left" part in parentheses because I am unsure about your requirements. If you only use inner joins (and not outer) then you will only get results for those cars that have the corresponding options and drivers in the respective tables.
0
 
LVL 1

Author Comment

by:tchris
ID: 26072763
???

What does XML have to do with anything?  I'm asking about SQL syntax.
0
 
LVL 1

Author Comment

by:tchris
ID: 26072790
Maybe just using subreports is the right way to go.  This probably isn't even a SQL issue...
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26072855
it is sql only, please try it
it will generate the out put as
Make
Ford | Explorer|   1990 |  White|  power steering,power sunroof,cd cd changer   | mickymouse, john
 
0
 
LVL 1

Author Comment

by:tchris
ID: 26072877
Even without left joins, they still multiply.

If I have three options and two drivers, I get six of each in the output:

Options:  Power Steering
               Power Sunroof
               CD Changer
               Power Steering
               Power Sunroof
               CD Changer

Drivers:  Mickey Mouse
               Jane Doe
               Mickey Mouse
               Jane Doe
               Mickey Mouse
               Jane Doe
0
 
LVL 1

Author Comment

by:tchris
ID: 26073014
shajukg,

I tried your syntax, and it does format the output differently.  But the multiplying output problem is the same.

I get something like this:

Options: Power Steering, Power Sunroof, CD Changer, Power Steering, Power Sunroof, CD Changer

Drivers:  Mickey Mouse, Jane Doe, Mickey Mouse, Jane Doe, Mickey Mouse, Jane Doe
0
 
LVL 20

Accepted Solution

by:
gatorvip earned 1000 total points
ID: 26073049
>>If I have three options and two drivers, I get six of each in the output:

That is the nature of joins...

1 (vehicle) x 3 (options/vehicle) x 2 (drivers/vehicle) will produce 6 results.

What you are looking for can't really be handled in Oracle, you will have to do this in the reporting software with 2 queries.
0
 
LVL 1

Author Closing Comment

by:tchris
ID: 31667342
This is the same conclusion I have come to.

Thanks for the help!
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26073189
try this

select v.make,v.model,v.year,v.color, LTRIM (EXTRACT (XMLAGG (XMLELEMENT ("x", ',' || vo.text)), '/x/text()'), ',') ,
from vehicle,
(select vehicle_id,LTRIM (EXTRACT (XMLAGG (XMLELEMENT ("x", ',' || vo.text)), '/x/text()'), ',') from vehicle v1,vehiclestooptions vo1,options o1
v1.id=vo1.vehicle_id and o1.id=vo1.option_id group by vehicle_id) vo,
(select vehicle_id,LTRIM (EXTRACT (XMLAGG (XMLELEMENT ("x", ',' || vo.name)), '/x/text()'), ',') from vehicle v2,Vehiclestodrivers vd2,drivers d2
v2.id=vd2.vehicle_id and d2.id=vd2.driver_id group by vehicle_id) vd,
where
v.id=vo.vehicle_id and
v.id=vd.vehicla_id and
group by v.make,v.model,v.year,v.color;
0
 
LVL 20

Expert Comment

by:gatorvip
ID: 26073327
@shajukg: your code, as written, did not compile for me, so I took the liberty to clean it up a little bit.

=======================================
select v.make,v.model,v.year,v.color,
  LTRIM (EXTRACT (XMLAGG (XMLELEMENT ("x", ',' || vo.text)), '/x/text()'), ',') options,
  LTRIM (EXTRACT (XMLAGG (XMLELEMENT ("x", ',' || vd.name)), '/x/text()'), ',') name
 
from vehicles v,
  (select vehicle_id,
    LTRIM (EXTRACT (XMLAGG (XMLELEMENT ("x", ',' || o1.text)), '/x/text()'), ',') text
    from vehicles v1, vehicles_to_options vo1, options o1
    where v1.id=vo1.vehicle_id and o1.id=vo1.option_id group by vehicle_id
  ) vo,
  (select vehicle_id,
    LTRIM (EXTRACT (XMLAGG (XMLELEMENT ("x", ',' || d2.name)), '/x/text()'), ',') name
    from vehicles v2,vehicles_to_drivers vd2,drivers d2
    where v2.id=vd2.vehicle_id and d2.id=vd2.driver_id group by vehicle_id
  ) vd
where
  v.id=vo.vehicle_id and
  v.id=vd.vehicle_id
group by v.make,v.model,v.year,v.color;
=======================================

that produces the following result:
MAKE                      MODEL                     YEAR COLOR      OPTIONS          NAME
Ford      Explorer      1990      White      Power Steering,Power Sun Roof,CD Changer      Jane Doe,Mickey Mouse
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question