SQL JOIN to omit other columns after first result

Question

Here is the result I need, simplified:

select name, phonenumber
from contacttmp
left outer join phonetmp on (contacttmp.id = phonetmp.contact_id);

 name  | phonenumber
-------+--------------
 bob   | 111-222-3333
 bob   | 111-222-4444
 bob   | 111-222-5555
 frank | 111-222-6666
 joe   | 111-222-7777

The query, however displays the name, I'm trying to omit the name after the first result:

 name  | phonenumber
-------+--------------
 bob   | 111-222-3333
       | 111-222-4444
       | 111-222-5555
 frank | 111-222-6666
 joe   | 111-222-7777

Here's how I made the example tables and the data:

create table contacttmp (id serial, name text);
create table phonetmp (phoneNumber text, contact_id integer);

select * from contacttmp;
 id | name
----+-------
  1 | bob
  2 | frank
  3 | joe

select * from phonetmp ;
 phonenumber  | contact_id
--------------+------------
 111-222-3333 |          1
 111-222-4444 |          1
 111-222-5555 |          1
 111-222-6666 |          2
 111-222-7777 |          3

Old part of question

I'm working on a contacts program in PHP and a requirement is to display the results but omit the other fields after the first record is displayed if there are multiple results of that same record.

From the postgres tutorial join examples I'm doing something like this with a left outer join:

SELECT *
FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);

    city      | temp_lo | temp_hi | prcp |    date    |     name      | location
--------------+---------+---------+------+------------+---------------+-----------
Hayward       |      37 |      54 |      | 1994-11-29 |               |
San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)

I can't figure out how to, or if it is possible to, alter the above query to not display the other fields after the first result.

For example, if we add the clause "WHERE location = '(-194,53)'" we don't want the second (and third if there is one) results to display the columns other than location, so the query (plus something extra) and the result would look like this:

SELECT *
FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name)
WHERE location = '(-194,53)';

    city      | temp_lo | temp_hi | prcp |    date    |     name      | location
--------------+---------+---------+------+------------+---------------+-----------
San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
              |         |         |      |            |               | (-194,53)

Is this possible with some kind of JOIN or exclusion or other query? Or do I have to remove these fields in PHP after getting all the results (would rather not do).

To avoid confusion, I'm required to achieve a result set like:

    city      | temp_lo | temp_hi | prcp |    date    |     name      | location
--------------+---------+---------+------+------------+---------------+-----------
San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
              |         |         |      |            |               | (-19,5)
              |         |         |      |            |               | (-94,3)
Philadelphia  |    55   |   60    | 0.1  | 1995-12-12 | Philadelphia  | (-1,1)
              |         |         |      |            |               | (-77,55)
              |         |         |      |            |               | (-3,33)

Where any additional results for the same record (city) with different locations would only display the different location.

Answer 1

You can do this type of logic in SQL, but it is not recommended. The result set from SQL queries is in a table format. Tables represented unordered sets and generally have all columns meaning the same thing.

So, having a result set that depends on the values from the "preceding" row is not a proper way to use SQL. Although you can get this result in Postgres, I do not recommend it. Usually, this type of formatting is done on the application side.

Answer 2

If you want to avoid repeating the same information, you can use a window function that tells you the position of that row in the group (a PARTITION for this purpose, not a group in the GROUP BY sense), then hide the text for the columns you don't want to repeat if that position in the group is greater than 1.

WITH joined_results AS (
   SELECT
      w.city, c.location, w.temp_lo, w.temp_hi, w.prcp, w.date,
      ROW_NUMBER() OVER (PARTITION BY w.city, c.location ORDER BY date) AS pos
   FROM weather w
       LEFT OUTER JOIN cities c ON (w.city = c.name)
   ORDER BY w.city, c.location
)
SELECT
   CASE WHEN pos > 1 THEN '' ELSE city END,
   CASE WHEN pos > 1 THEN '' ELSE location END,
   temp_lo, temp_hi, prcp, date
FROM joined_results;

This should give you this:

     city      | location  | temp_lo | temp_hi | prcp |    date    
---------------+-----------+---------+---------+------+------------
 Hayward       |           |      37 |      54 |      | 1994-11-29
 San Francisco | (-194,53) |      46 |      50 | 0.25 | 1994-11-27
               |           |      43 |      57 |    0 | 1994-11-29

To understand what ROW_NUMBER() OVER (PARTITION BY w.city, c.location ORDER BY date) AS pos does, it probably worth looking at what you get with SELECT * FROM joined_results:

     city      | location  | temp_lo | temp_hi | prcp |    date    | pos 
---------------+-----------+---------+---------+------+------------+-----
 Hayward       |           |      37 |      54 |      | 1994-11-29 |   1
 San Francisco | (-194,53) |      46 |      50 | 0.25 | 1994-11-27 |   1
 San Francisco | (-194,53) |      43 |      57 |    0 | 1994-11-29 |   2

After that, just replace what you don't want with white space using CASE WHEN pos > 1 THEN '' ELSE ... END.

(This being said, it's something I'd generally prefer to do in the presentation layer rather than in the query.)

Answer 3

Consider the slightly modified test case in the fiddle below.

Simple case

For the simple case dealing with a single column from each column, comparing to the previous row with the window function lag() does the job:

SELECT CASE WHEN lag(c.contact) OVER (ORDER BY c.contact, p.phone_nr)
               = c.contact THEN NULL ELSE c.contact END
     , p.phone_nr
FROM   contact c
LEFT   JOIN phone p USING (contact_id);

You could repeat that for n columns, but that's tedious

For many columns

SELECT c.*, p.phone_nr
FROM  (
   SELECT *
        , row_number() OVER (PARTITION BY contact_id ORDER BY phone_nr) AS rn
   FROM   phone
   ) p
LEFT  JOIN contact c ON c.contact_id = p.contact_id AND p.rn = 1;

Something like a "reverse LEFT JOIN". This is assuming referential integrity (no missing rows in contact. Also, contacts without any entries in phone are not in the result. Easy to add if need should be.

SQL Fiddle.

Aside, your query in the first example exhibits a rookie mistake.

SELECT * FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name)
WHERE location = '(-194,53)';

One does not combine a LEFT JOIN with a WHERE clause on the right table. Doesn't makes sense. Details:

• Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail

Except to test for existence ...

• Select rows which are not present in other table