Вывод одной строки на каждое имя с учетом метки

Question

Есть таблица stuff_id с полями id и name.
Есть таблица stuff_phone с полями id, phone, preference.

У одного сотрудника может быть несколько телефонов. У одного из его телефонов может быть предпочтение, в таком случае для этого телефона в поле preference стоит какая-то метка, скажем, 'p'.

Нужен запрос, который выводит информацию в виде "имя - номер" для всех сотрудников, причём для каждого сотрудника нужно вывести только один номер, и если у него есть номер с предпочтением, то именно этот, в противном случае любой его.

Как написать такой запрос?

UPD: Для связи таблиц используется поле id. Можно считать поле preference битовым - не суть. Хотелось бы какое-то стандартное ANSI решение, которое работало бы везде, а так всё равно на какой реализации. Главное - суть вопроса, а не нюансы. Как сделать такое объединение таблиц, чтобы имени ставился в соответствие один номер из множества?

Answer 1

Можно подзапросом в поле:

SELECT stuff_id.Id, stuff_id.name,
   (select top 1 stuff_phone.phone from stuff_phone where stuffid = stuff_id.Id 
  order by  stuff_phone.preference) phone
FROM stuff_id 

(для MSSQL работать будет)

Для MySQL и других СУБД, поддерживающих LIMIT, вместо top:

SELECT stuff_id.Id, stuff_id.name,
   (select stuff_phone.phone from stuff_phone where stuffid = stuff_id.Id 
     order by stuff_phone.preference
     limit 1) phone
FROM stuff_id 

Раз уж затронута тема первая строка из выборки, то приведу как звучит это в разных субд:

• MSSQL,SYBASE,db2: select top 1 stuff_phone.phone from stuff_phone where stuffid = stuff_id.Id
• informix,firebird select first 1 stuff_phone.phone from stuff_phone where stuffid=stuff_id.Id
• oracle <12 select stuff_phone.phone from stuff_phone where stuffid = stuff_id.Id and rownum=1
• oracle 12 select stuff_phone.phone from stuff_phone where stuffid = stuff_id.Id fetch first 1 rows only
• MySql select stuff_phone.phone from stuff_phone where stuffid = stuff_id.Id limit 1

Answer 2

ANSI решение:

SELECT st.Id, st.name, coalesce(max(ph1.phone),max(ph2.phone)) as phone
  FROM stuff as st
  LEFT JOIN stuff_phone ph1 ON st.id = ph1.id and ph1.preference='p'
  LEFT JOIN stuff_phone ph2 ON st.id = ph2.id and ph2.preference!='p'
 GROUP BY st.Id, st.name

Или:

SELECT st.Id, st.name,
       coalesce(max(case when ph1.preference='p' then ph1.phone end),
                max(ph1.phone)) as phone
  FROM stuff as st
  LEFT JOIN stuff_phone ph1 ON st.id = ph1.id
 GROUP BY st.Id, st.name

Но конечно, практически в любой современной СУБД найдется более быстрое решение.

Answer 3

Попробуйте такой вариант, но может будет предложен лучше

SELECT st.Id, st.name, ph.phone FROM stuff_id as st
INNER JOIN  stuff_phone as ph ON ph.stuffid = st.Id 
WHERE ph.Id = 
(SELECT Top 1 id from stuff_phone 
WHERE st.Id = stuff_phone.stuffId 
Order By stuff_phone.preference)
GROUP BY st.Id, st.name, ph.phone

Answer 4

Вот запрос по стандарту ANSI, должен работать на любом бренде

SELECT s.*, p.phone  
FROM stuff AS s 
LEFT JOIN (
    SELECT id, phone, preference, 
        row_number() OVER (PARTITION BY id ORDER BY preference) rnum
    FROM phone_stuff) AS p ON p.id = s.id and p.rnum = 1