There is a stuff_id table with id and name fields.
There is a table stuff_phone with fields id , phone , preference .

One employee may have multiple phones. One of his phones may have a preference, in which case for this phone there is some kind of label in the preference field, say 'p' .

We need a request that displays information in the form of "name - number" for all employees, and for each employee you need to display only one number, and if he has a number with a preference, then this one, otherwise any.

How to write such a request?

UPD: For linking tables, the id field is used. You can consider the preference field bitwise - not the point. I would like some kind of standard ANSI solution that would work everywhere, and no matter what implementation. The main thing is the essence of the issue, not nuances. How to make a table join so that a name is assigned to one number from a set?

  • 3
    First, add stuff_id to the number table to connect the number with the person, otherwise how will you understand which number belongs to what? The preference field is a flag, so make it according to the DB dialect in the bit or integer field, so that the values ​​are 0 or 1. And then read about the subqueries. - teran
  • and yes, specify the type of SQL: mysql , sql server , oracle , ms access , etc. in question tags - teran
  • And at the same time, specify whether it is necessary to display an employee record if he does not have a single phone. - Akina

3 answers 3

You can subquery in the field:

 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

(for MSSQL it will work)

For MySQL and other DBMS that support LIMIT , instead of 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

Since the topic is the first line of the sample, I will give you how it sounds in different subd:

  • 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
  • For db2 too, thanks :) - zer_ik
  • I added a MySQL option to your answer, otherwise they ask the same thing about MySQL, I’m going to do a link here - Mike
  • @Mike saw the question, did not get it. I am for. Thank. - nick_n_a

ANSI solution:

 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

Or:

 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

But of course, in almost any modern DBMS, there will be a faster solution.

  • Yes, it also works, thanks) - zer_ik

Try this option, but it may be offered better.

 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
  • Is the query too complicated? If you make a subquery, then why is it necessary? - teran
  • @teran what do you mean? to remove join from a subquery? - user2455111
  • Look at the next answer with a subquery and no joins. Here either purely subquery, or are necessary with grouping. - teran
  • @teran grouping added, of course, if it meant. - user2455111
  • It seems to me that thanks to top 1 , the sample will return one phone number. There are several employees, and the number one is ph.ID= . - nick_n_a