Oracle SQL - table join


Just need some advice on my query.

I have three tables.   Table_1 shows the current status of an order, Table_2 shows historical data on the teams that have worked the order, Table_3 shows the department that the teams are in.

SELECT T1.Key, T1.crrnt_id, T2.From_Team, T3.Teammaster
FROM table_1 T1, table_2 T2, table_3 T3
WHERE Table_3.TeamMaster = 'Main'
AND Table_1.Crnt_Teammaster ='Non-Main'
AND Table_1.Key = Table_2.Key (+)
AND Table_2.From_Team = Table_3.Team_Id (+)

I get two results:
2, 1c, Non-Main, 1a, Main
2, 1c, Non-Main, 1b, Main

If the Where criteria is true, I only want to see one result.  On a larger scale this can return dozens of results if an order is currently in a non-main bucket, but has been transferred from Main buckets repeatedly.  Is there are way to show only one result per Key if the WHERE condition is true?


Below are some examples on the three tables:

Table 1		
Key	crrnt_id	Crnt_tmmstr
1	1b	        Main
2	1c	        Non-Main

         Table 2		
Key	from_team	to_team
1	1a	        1b
2	1a	        1c
2	1c	        1b
2	1b	        1c

         Table 3	
team_id  	teammaster
1a	        Main
1b	        Main
1c	        Non-Main

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

trying to see where you are with this - you have 4 fields in the select query, but show 5 in your results?

TelMacoAuthor Commented:

SELECT T1.Key, T1.crrnt_id, T1.Crnt_tmmstr, T2.From_Team, T3.Teammaster
you have a teammaster in team 3 table, is that the same as teammaster in table 1? would you need to equate those as well?
Not sure what you are trying to accomplish, since the results are what you asked for - the fact that there are 2 FromTeams for the record selection is true!
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

TelMacoAuthor Commented:
They are not the quite the same, table one shows the current status of the key & the team it is assigned to and that teams team master.

Table 3 is basically a lookup table, for all the team ids, and their masters.  This is needed b/c table 2 does not show the master.

What I want it to minimize my results.  I do not want two rows to be returned, only one.

I want a query that shows all Keys in Table 2 that are in Table 1 under a Non-Main Master Team, but have been assigned from (in table 2) a Main Master Team

If those keys have been transferred more than once, I am getting multiple results.  I only want one row per key in table 2 if it meets the criteria.
TelMacoAuthor Commented:

Ok, I think I figured it out.  Simple once I realized what I was doing wrong...I should have explained this better.  The only important info being returned is the Key, crrnt_id, and crrnt_tmmaster, all from Table_1.  The fields from Table_2 and 3 were just there to check against, I've realized that I really didn't need them shown in the results.

If I remove those last two fields from my query, I'm left with:

2, 1c, Non-Main
2, 1c, Non-Main

Changing the query then to:

SELECT UNIQUE T1.Key, T1.crrnt_id, T1.Crnt_tmmstr

Gives me just the one line of data that I want.  The query is awfully slow, but it works.

2, 1c, Non-Main
SharathData EngineerCommented:
try this.
                 OVER(PARTITION BY T1.KEY,T1.crrnt_id,T2.From_Team ORDER BY T2.From_Team, T3.Teammaster) rn
        FROM   table_1 T1, 
               table_2 T2, 
               table_3 T3 
        WHERE  Table_3.TeamMaster = 'Main' 
               AND Table_1.Crnt_Teammaster = 'Non-Main' 
               AND Table_1.KEY = Table_2.KEY (+) 
               AND Table_2.From_Team = Table_3.Team_Id (+)) AS t1 
WHERE  rn = 1

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TelMacoAuthor Commented:
That  was close, but not quite there.  I think it's just b/c I had included the fields from Table 2 and 3.  I really don't need them showing.  When I tried your suggestion I still got multiple results per T1.KEY

Once I exclude T2.From_Team, and T3.Teammaster from the SELECT portion of the query though, I get what I'm after, so no need to modify it.

I think your suggestion might even be a bit faster than the SELECT UNIQUE that I was using.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.