many to many relations

• lak.konduri

  Newbie

  Points: 1

  More actions

  October 21, 2012 at 11:11 am

  #268782

  I have one table and the columns are bookingreference,complaintreference,category1,category2,desriptionofthecomplaint,

  one bookingref have one complaintid,

  one complaintid have many bookingreferences,

  one complaintid have many categories and one category have many complaintid,

  one complaintid have many category2 and one category 2 have many complaints.

  I need to prepare the data into tables and load them to prepare reports on it.

  can anyone suggest me the best approach.

  Thanks pavan

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  October 21, 2012 at 12:26 pm

  #1551176

  lak.konduri (10/21/2012)

  ---

  I have one table and the columns are bookingreference,complaintreference,category1,category2,desriptionofthecomplaint,

  one bookingref have one complaintid,

  one complaintid have many bookingreferences,

  one complaintid have many categories and one category have many complaintid,

  one complaintid have many category2 and one category 2 have many complaints.

  I need to prepare the data into tables and load them to prepare reports on it.

  can anyone suggest me the best approach.

  Thanks pavan

  Depending on what the reporting requirements actually are, it would seem that LEFT OUTER JOINing all of the other tables to the BookingRef table on the ComplaintID would do the trick. This doesn't actually look like a many-to-many problem because of that.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  October 21, 2012 at 3:27 pm

  #1551190

  It might be easier for Jeff, or anyone else to give you a more detailed answer if you would illustrate what one or two of your reports look like. Depending on the data you want, a JOIN, a LEFT JOIN, or even a CROSS APPLY of a subquery might be appropriate ways to tie data together from multiple tables.

  __________________________________________________

  Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
  Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

• ScottPletcher

  SSC Guru

  Points: 100947

  More actions

  October 22, 2012 at 2:22 pm

  #1551636

  lak.konduri (10/21/2012)

  ---

  I have one table and the columns are bookingreference,complaintreference,category1,category2,desriptionofthecomplaint,

  one bookingref have one complaintid,

  one complaintid have many bookingreferences,

  one complaintid have many categories and one category have many complaintid,

  one complaintid have many category2 and one category 2 have many complaints.

  I need to prepare the data into tables and load them to prepare reports on it.

  can anyone suggest me the best approach.

  Thanks pavan

  I don't even see a complaintid in your table.

  I do think you will need multiple tables (more than 2), but the rest is so sketchy I couldn't say what they were at this point.

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.