SQL Query, Trying to improve efficiency (Oracle DBMS)

Question

For the following tables (I've used ** to indicate the parts of the primary keys)

Team (*tid*, name)
Game (*gid*, tid, name, year)
Member (*mid*, name)
MemberOf (*mid*, *tid*, startyear, endyear, position)

I need to Show The History of each Striker (position) If startyear is NULL use the year from the earliest game for that player for that team Result: Striker Name, Team Name, Start Year, End Year Sorted by the Strikers name, then the start year

Whilst I have a solution I dont think it very efficient The Execution plan on infers a node cost of 28. Was Hoping for improvement suggestions and explanations?

Have you tried UNION ALL? Those look like mutually exclusive queries to me and UNION ALL doesn't try to filter out duplicates. Of course make sure that UNION ALL returns exactly the same results. — HLGEM
– HLGEM, Commented Feb 4, 2013 at 19:01
I don't see any fields that would enable me to find the year of the earliest game for a specific team. Does your game table have a date field that you are not displaying? — Dan Bracuk
– Dan Bracuk, Commented Feb 4, 2013 at 19:14
Thanks HLGEM, The ALL saved one on the Node Cost Dan, Your right, I missed it! Edited to add in — Hector
– Hector, Commented Feb 4, 2013 at 20:09

Gordon Linoff · Accepted Answer · 2013-02-04 20:15:14Z

2

You can do this with one query, using left outer join and coalesce (if I have the logic correct):

SELECT Striker_Name, Team_Name, coalesce(MO.STARTYEAR, g.start_year), MO.ENDYEAR
FROM (SELECT NAME AS Striker_Name, MID
      FROM MEMBER
      ) ME JOIN
      (SELECT MID, TID, STARTYEAR AS Start_Year, ENDYEAR AS End_Year
       FROM MEMBEROF
       WHERE POSITION = 'striker'
      ) MO ON ME.MID = MO.MID left outer JOIN
      (SELECT NAME AS Team_Name, TID
       FROM TEAM
      ) T
      ON MO.TID = T.TID left outer JOIN
      (SELECT GID, MIN(GAME.YEAR) AS Start_Year FROM GAME GROUP BY TID
      ) G
      ON G.TID = MO.TID
ORDER BY Striker_Name, Start_Year

You seem to be filling in the start year from the earliest game. Just do the join and decide in the select which one to use.

edited Feb 4, 2013 at 20:15

answered Feb 4, 2013 at 19:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Hector Over a year ago

Thanks, it appears to be close to what I am after, however this seems to only return the results where STARTYEAR is NULL?

Gordon Linoff Over a year ago

@user2036256 . . . I think I missed one of the joins. They should all be left outer joins.

Hector Over a year ago

The only other join is the first one. Changing it to a left outer just causes all Members to be listed!

Gordon Linoff Over a year ago

@user2036256 . . . Duh. That condition was in the subquery. Silly mistake. The whole purpose of using one query was to get rid of that condition (as I just did).

Hector Over a year ago

Ahh, crap, Can't believe I didnt spot that either. Thanks! Havn't seen coalesce before. I think i need to take a few more sql tutorials

Collectives™ on Stack Overflow

SQL Query, Trying to improve efficiency (Oracle DBMS)

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related