Joining a row from tableA to 2 rows from tableB

I have tableA with fields PlayerID, Year, Stat and tableB with fields PlayerID, Year, Stat where both tables will contain several records for each person.

I want to get the maximum year and corresponding stat for a given player from Table A, and the next two years and corresponding stats for the same player from table B.

Please assume the table structure cannot be changed.

I have been able to do this for a specific person with a 3-row results table this way:

(SELECT PlayerID, Year, Stat
FROM tableA
WHERE PlayerID = 123
AND Year = (SELECT MAX(Year) FROM tableA where PlayerID = 123))
UNION (SELECT PlayerID, Year, Stat
FROM tableB
WHERE PlayerID = 123
AND year = (SELECT MAX(Year)+1 FROM tableA where PlayerID = 123))
UNION (SELECT PlayerID, Year, Stat
FROM pro
WHERE PlayerID = 123
AND year = (SELECT MAX(Year)+2 FROM tableA where PlayerID = 123))
  1. How would I change this query so that the results lie in a single row:
    PlayerID, YearA, StatA, YearB1, StatB1, YearB2, StatB2 where YearB1 and YearB2 are the two years from tableB?

  2. Finally, how can that code be adjusted to result in a table containing the rows of information for each distinct person from tableA?

So obvious first question is… why do you have 2 tables for the same structure of data?

Anyway.

The query can result in a single row by JOIN’ing instead of UNION’ing - unioning adds rows, joining adds columns. You then select * from your joined subqueries. (Rare time when select * is valid…)

Doing it for every person… that… is beyond my level of query, i’d be more inclined to punt that function back to the querier.

Would you mind telling us why it cant be changed. Can it be added to? If it really cant be changed, you could create a View which will give you a single table of all the records and make your query much simpler.

@benanamen, @m_hutley - thank you for responding. The tables can’t be changed because they are greatly simplified versions of very large tables of the same structure not of making, over which I have absolutely no control. I am trying to help someone solve a problem based on those tables, but I can’t figure out how to connect the first table to two rows of the second table.

I will try the JOINS and see what results I can get. I’ve never tried creating views, but will give that a go too. I might not be able to get back to this for a day.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.