• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

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?
0
tchris
Asked:
tchris
  • 7
  • 4
  • 3
  • +1
1 Solution
 
YZlatCommented:
try crystal reports
0
 
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

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

I really want to get this SQL syntax figured out better though.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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.
0
 
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;
0
 
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.
0
 
tchrisAuthor Commented:
???

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

Thanks for the help!
0
 
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;
0
 
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 7
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now