Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Let's say I have four tables:

TABLE:    players
COLUMNS:  id, first_name, last_name

TABLE:    passing_stats
COLUMNS:  id, year, passing_yards (several other passing columns)

TABLE:    rushing_stats
COLUMNS:  id, year, rushing_yards (several other rushing columns)

TABLE:    receiving_stats
COLUMNS:  id, year, receiving_yards (several other receiving columns)

Let's say Michael Vick has an id of 100. I want to get his full name and all of his stats for each year (passing, rushing, and receiving).

I don't want any duplicates, meaning that rushing stats for 2011 should appear in the same row as passing stats for 2011.

What is the most elegant way to write this query? Thanks.

share|improve this question
add comment

3 Answers

The following will work in Postgres. Test here: SQL-Fiddle, postgres-test. SQL-Server does not have NATURAL JOIN and MySQL has NATURAL but doesn't have FULL joins:

SELECT
    id,
    first_name,
    last_name,
    year,
    passing_yards,
    rushing_yards,
    receiving_yards
  FROM 
      players p 
    NATURAL LEFT JOIN 
      ( passing_stats pas
      NATURAL FULL JOIN 
        rushing_stats rus
      NATURAL FULL JOIN 
        receiving_stats rec 
      )
share|improve this answer
    
+1 The OP will have to test which query is faster. Yours the most elegant so far in any case. –  Erwin Brandstetter Apr 12 '12 at 7:34
2  
@Erwin: But it will break if there are any other two columns with identical names in the tables. –  ypercube Apr 12 '12 at 7:38
1  
I can't remember any other case where parentheses were indeed needed in a Join. –  ypercube Apr 12 '12 at 7:42
add comment

Test case:

CREATE TEMP TABLE players (id int, first_name text, last_name text);
INSERT INTO players VALUES
 (1, 'jon',  'foo')
,(2, 'jane', 'bar');

CREATE TEMP TABLE passing_stats (id int, year int, passing_yards int);
INSERT INTO passing_stats VALUES
 (1, 2007, 127)
,(1, 2008, 128)
,(1, 2009, 129)
,(1, 2010, 130)
,(2, 2007, 111);

CREATE TEMP TABLE rushing_stats (id int, year int, rushing_yards int);
INSERT INTO rushing_stats VALUES
 (1, 2007, 137)
,(1, 2008, 138)
,(1, 2010, 139)
,(2, 2007, 111);

CREATE TEMP TABLE receiving_stats (id int, year int, receiving_yards int);
INSERT INTO receiving_stats VALUES
 (1, 2007, 147)
,(1, 2008, 148)
,(1, 2011, 149)
,(2, 2007, 111);

Query 1

This is basically a "proper" version of what @kgrittn posted. My fixes are somewhat invasive, so I posted another answer. Requires PostgreSQL 8.4 or newer.

WITH x AS (SELECT 1 AS id)
   , y AS (
  SELECT year FROM x JOIN passing_stats USING (id)
  UNION
  SELECT year FROM x JOIN rushing_stats USING (id)
  UNION
  SELECT year FROM x JOIN receiving_stats USING (id)
  )
SELECT p.first_name
      ,p.last_name
      ,y.year
      ,s.passing_yards
      ,r.rushing_yards
      ,c.receiving_yards
FROM  (x CROSS JOIN y)
JOIN   players p USING (id)
LEFT   JOIN passing_stats s USING (id, year)
LEFT   JOIN rushing_stats r USING (id, year)
LEFT   JOIN receiving_stats c USING (id, year)
ORDER  BY y.year

Query 2

After @ypercubes question about "FULL NATURAL JOIN" I realized there is better way. Shorter syntax and should be faster, too.

SELECT p.first_name
      ,p.last_name
      ,COALESCE(s.year, r.year, c.year) AS year
      ,s.passing_yards
      ,r.rushing_yards
      ,c.receiving_yards
FROM              (SELECT * FROM passing_stats   WHERE id = 1) s
FULL   OUTER JOIN (SELECT * FROM rushing_stats   WHERE id = 1) r USING (id, year)
FULL   OUTER JOIN (SELECT * FROM receiving_stats WHERE id = 1) c USING (id, year)
JOIN   players p USING (id)
ORDER  BY COALESCE(s.year, r.year, c.year);

Identical results. @ypercube's idea with NATURAL JOIN is even better - provided there are no duplicate names across all tables.

share|improve this answer
    
Yeah, without test data I'm not surprised I messed that up. Thanks for spotting the lack of the joins to x in the second CTE. Was there anything else substantive, or was the rest stylistic preference? BTW, I tend to stay away from USING in complex queries since it has surfaced that it introduces optimization barriers in certain cases and I hate to spend the time sorting out whether it's an issue for a particular query. Pity, it's nice syntax. –  kgrittn Apr 12 '12 at 5:23
1  
@kgrittn: The double WITH was also invalid, the rest is a matter of taste, style and shorter syntax. That thing about the USING clause is troubling news for me as I use it a lot. Do you happen to have a link to more information about this? –  Erwin Brandstetter Apr 12 '12 at 5:50
1  
I tried to search the PostgreSQL archives for it, but I couldn't remember how far back it was, or even for sure which list it was discussed on -- and since the search engine stems the words, it turned up every post with words "use", "uses", etc. I lost patience before completing a sequential search. :-( I believe the issue had to do with propagation of constant quals, but the details have gone fuzzy on me. The good news is that it only bites you in certain corner cases, so I still use it for simple cases. And I make that double WITH mistake all the time. Testing cathces it. :-) –  kgrittn Apr 12 '12 at 6:05
    
@kgrittn: Thanks for your effort! I am very interested and posted a question about it. –  Erwin Brandstetter Apr 12 '12 at 8:09
add comment

You didn't provide the DDL and data to set this up, so this is untested, but something along these lines should work under PostgreSQL. I think this is mostly standard-conforming, so I would expect it to work on other products with little or no change.

WITH x(id) AS (SELECT 100),
     y(year) AS (
  SELECT year FROM passing_stats t1 JOIN x ON t1.id = x.id
  UNION
  SELECT year FROM rushing_stats r1 JOIN x ON r1.id = x.id
  UNION
  SELECT year FROM receiving_stats c1 JOIN x ON c1.id = x.id)
SELECT
    p.first_name,
    p.last_name,
    y.year,
    t.passing_yards,
    r.rushing_yards,
    c.receiving_yards
  FROM x
  JOIN y ON TRUE
  JOIN players p ON p.id = x.id
  LEFT JOIN passing_stats t ON t.id = x.id AND t.year = y.year
  LEFT JOIN rushing_stats r ON r.id = x.id AND r.year = y.year
  LEFT JOIN receiving_stats c ON c.id = x.id AND c.year = y.year;
share|improve this answer
1  
+1 Elegant way to gather the relevant years - even if it does not work the way you have it. –  Erwin Brandstetter Apr 12 '12 at 3:57
    
Edited to fix errors in query. –  kgrittn Apr 12 '12 at 12:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.