Using a Master table for ordering all the reports.

Question

0.00/5 (No votes)

See more:

SQL-server-2000

SQL-Server-2005

, +

Hi All,

We have a number of reports having two-three fields that are always displayed in a particular order.

For convenience, the DB architect has designed a master ordering table that saves the ordering of the 2-3 fields with numerical values assigned to them. i.e.
Ordering_Master

Group1 	Order1	        Group2		Order2
PAM	1	 	HNWI		1
PAM	1	 	Hedge Funds	2
PAM	1	 	PFs		3
PAM	1	 	ETFs		4
PIM	1	 	Pvt Insts	1
PIM	1	 	Banks		2
PIM	1	 	Trusts		3

Now, we have another table that is used for driving almost all of our reports i.e. the
Transaction_Master

Id	      Name	   Record_Type	Close_Date	Status	Role__c	Group1 Group2       
a0L7000000Cyk0fEAB	84001166 	Open Opportunities	00:00.0	 Forecast	Sales - Gov - West & MidWest	PAM	ETFs
a0L7000000Cyk0gEAB	84001167	 Open Opportunities	00:00.0	 Forecast	Sales - Gov - West & MidWest	PAM	 PFs
a0L7000000Cyk0hEAB	84001168	 Open Opportunities	00:00.0	 Forecast	Sales - Gov - West & MidWest	PAM	 HNWI
a0L7000000A1kEeEAJ	82949995	 Open Opportunities	00:00.0	 Forecast	Business - Index and Analytics	PIM	Banks
a0L7000000AusDkEAJ	83431781	 Open Opportunities	00:00.0	 Forecast	Sales - Gov - Boston	PAM	HNWI

Now, I am trying to make a join of the above 2 tables i.e. of the Transaction_Master & Ordering_Master tables on the Group1 & Group2 fields, however I am getting repeating rows.

My aim to join these two tables & get the Order1 & Order2 columns from the Ordering_Master table and then sort the resultset by the Order1 and then the Order2 column i.e.
Order By Order1 , Order2

Any help would be much appreciated.

Thanks,
Aryan.

Posted 23-Oct-12 21:26pm

aakar

Updated 24-Oct-12 4:54am

Sandeep Mewara

v2

Add a Solution

Comments

Maciej Los 24-Oct-12 14:21pm

Show us what you have done till now...

Aadhar Joshi 26-Oct-12 6:23am

Please put here your query

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

kankeyan · Answer 1 · 2012-10-26T02:15:00

Solution 1

SQL

select order1, order2
from transaction_Master tm
    Join ordering_master om ON tm.group_1= om.group1 and tm.group_2=om.group2
order by order1, order2

Posted 26-Oct-12 2:15am

kankeyan

Updated 26-Oct-12 4:41am

v2

fjdiewornncalwe · Answer 2 · 2012-10-26T05:14:00

Solution 2

If you are getting repeating rows, just add a DISTINCT clause in. Or am I missing something in your question?

Posted 26-Oct-12 5:14am

fjdiewornncalwe

kankeyan · Answer 3 · 2012-10-26T19:49:00

Solution 3

Distinct is not recommend always.

Just use this query

SQL

select order1, order2
from transaction_Master tm
    Join ordering_master om ON tm.group_1= om.group1 and tm.group_2=om.group2
order by order1, order2

Posted 26-Oct-12 19:49pm

kankeyan