Multiple many-to-many joins

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?
LVL 1
tchrisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

YZlatCommented:
try crystal reports
gatorvipCommented:
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

tchrisAuthor Commented:
Already using crystal reports, which has issues of its own. :-/

I really want to get this SQL syntax figured out better though.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

tchrisAuthor Commented:
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.
Shaju KumbalathDeputy General Manager - ITCommented:
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;
gatorvipCommented:
>>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.
tchrisAuthor Commented:
???

What does XML have to do with anything?  I'm asking about SQL syntax.
tchrisAuthor Commented:
Maybe just using subreports is the right way to go.  This probably isn't even a SQL issue...
Shaju KumbalathDeputy General Manager - ITCommented:
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
 
tchrisAuthor Commented:
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
tchrisAuthor Commented:
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
gatorvipCommented:
>>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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tchrisAuthor Commented:
This is the same conclusion I have come to.

Thanks for the help!
Shaju KumbalathDeputy General Manager - ITCommented:
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;
gatorvipCommented:
@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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.