Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

many to many relations Expand / Collapse
Author
Message
Posted Sunday, October 21, 2012 11:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 18, 2012 9:17 AM
Points: 1, Visits: 24
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
Post #1375185
Posted Sunday, October 21, 2012 12:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:19 AM
Points: 35,979, Visits: 30,270
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1375194
Posted Sunday, October 21, 2012 3:27 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:45 AM
Points: 3,949, Visits: 5,944
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? -- Stephen Stills
Post #1375209
Posted Monday, October 22, 2012 2:22 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:38 AM
Points: 1,746, Visits: 2,554
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)
I'm not fat, I'm gravity challenged.
Post #1375724
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse