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 @ 8:54 PM
Points: 37,075, Visits: 31,634
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."

(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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 4,013, Visits: 6,098
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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:14 PM
Points: 2,083, Visits: 3,126
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)

"While in these days of quiet desperation /
As I wander through the world in which I live /
I search everywhere for some new inspiration /
But it's more than cold reality can give /
If I need a cause for celebration /
Or a comfort I can use to ease my mind /
I rely on my imagination /
And I dream of an imaginary time" : the inimitable Mr. Billy Joel
Post #1375724
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse